Pobieranie liczb i cyfr z tekstu w SQL Server
Tym razem zajmę się problemem pobierania cyfr, liczb, numerów z dowolnego ciągu znaków. Przypuśćmy, że dane w kolumnie lub zmiennej zapisane są w formacie NUMXXXX, gdzie XXXX to kolejne numery. Rekordy miałyby więc wartości NUM0001, NUM0002, NUM9456. Jak wydobyć z takich symboli numer? Jeżeli format jest znany, wystarczy prosta instrukcja SUBSTRING. Jeżeli format jest mniej precyzyjny, tj. jeżeli przedrostki mogą być różnej długości, wtedy sprawa nie jest tak prosta. Co wtedy, gdy cyfra zawiera separatory w postaci przecinka? Przedstawię jedną z technik, która może znaleźć zastosowanie w takich przypadkach.
Technika polegająca na wycinaniu znaków spoza zakresu 0-9
Technika w założeniu bajecznie prosta. Z podanego łańcucha znaków wycięte zostaną wszystkie elementy, które nie są cyframi. Przyjrzyjmy się fragmentowi skryptu zaprezentowanemu poniżej:
CREATE FUNCTION dbo.GetDigits(@str nvarchar(MAX)) RETURNS nvarchar(MAX) AS
BEGIN
DECLARE @pos int
SELECT @pos = PATINDEX('%[^0-9]%', @str)
WHILE @pos > 0
BEGIN
SET @str = STUFF(@str, @pos, 1, '')
SELECT @pos = PATINDEX('%[^0-9]%', @str)
END
RETURN @str
END
GO
--Przykładowe wywołania funkcji
SELECT dbo.GetDigits('N123') --123
SELECT dbo.GetDigits('123.456.789') --123456789
SELECT dbo.GetDigits('FX987-23') --98723
SELECT dbo.GetDigits('98#$T$99%$H6B%$GBRT#%3453fsdf09&') --98996345309
Technika rozbijania sekwencji na szereg liczb
Czasami powyższa technika nie jest adekwatna do napotkanej sytuacji. Zdarza się, że wejściowa sekwencja zawiera więcej niż jedną liczbę i każda z nich ma znaczenie. W takim przypadku lepiej podany ciąg wejściowy rozbić i zwrócić poszczególne liczby w formie tabeli jednokolumnowej. Przykład takiego rozwiązania pokazany jest na poniższym listingu:
RETURNS @ret TABLE(value int) AS
BEGIN
DECLARE @pos int
DECLARE @lastPos int = 1
SELECT @pos = PATINDEX('%[^0-9]%', @str)
WHILE @pos>0
BEGIN
IF (@pos <> @lastPos)
BEGIN
INSERT INTO @ret
VALUES (CAST(SUBSTRING(@str, @lastPos, @pos-@lastPos) AS int))
SET @lastPos = @pos
END
SET @str = STUFF(@str, @pos, 1, '')
SELECT @pos = PATINDEX('%[^0-9]%', @str)
END
IF (@lastPos != LEN(@str)+1)
INSERT INTO @ret
VALUES (CAST(SUBSTRING(@str, @lastPos, LEN(@str)+1-@lastPos) AS int))
RETURN
END
GO
--Przykładowe wywołania funkcji
SELECT * FROM dbo.GetNumbers('F145-D12') --145, 12
SELECT * FROM dbo.GetNumbers('123.456.789') --123, 456, 789
SELECT * FROM dbo.GetNumbers('98#$T$99%$6B%$GT#%3453fsdf09&') --98, 99, 6, 3453, 9
W wyniku tej operacji otrzymujemy tabelę z jedną kolumną, w której poszczególne rekordy zawierają kolejne napotkane liczby.
Być może są inne, skuteczniejsze metody. Zachęcam do dzielenia się nimi w komentarzach.
Kategoria:SQL Server
Komentarze:
Troche zmienilem i smiga'
USE [LiczbyWStringu]
GO
/****** Object: UserDefinedFunction [dbo].[fnGetNumbersDecStringNew] Script Date: 11.11.2022 09:31:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Moje modyfikacje dla warunku @Licznik
CREATE FUNCTION [dbo].[fnGetNumbersDecStringNew](@str nvarchar(MAX),@Licznik int)
RETURNS @ret TABLE(value decimal(10,2)) AS
BEGIN
--Declare @ret1 TABLE(value nvarchar(255))
Declare @i int
---Declare @str nvarchar(255)
Declare @p nvarchar(255)
Declare @d nvarchar(255)
Declare @LP int
--Declare @Licznik int
--set @licznik =4
set @LP=0
set @p=''
--set @str = 'uaj1s/k60/23.24kl 5MM 34.53C89Lm naj/890.0sie'
set @i = 0
set @d =''
While @i< Len(@str)
Begin
set @i+=1
set @p = SUBSTRING(@str, @i ,1)
If @p>= char(48) and @p < =char(57) or @p= char(46)
Begin
set @d = @d + @p --+','
---char(47) = /
--If SUBSTRING(@k, @i+1,1)<char(48) or SUBSTRING(@k, @i+1,1)>char(57)-- and SUBSTRING(@k, @i+1,1)<char(46)
If SUBSTRING(@str, @i+1,1)<char(46)or SUBSTRING(@str, @i+1,1)=char(47) or SUBSTRING(@str, @i+1,1)>char(57)
or SUBSTRING(@str, @i+1,1)=char(64)
begin
set @lp=@LP+1
If @lp =@licznik
--INSERT INTO @ret VALUES (CAST(@d AS decimal(10,2)))
INSERT INTO @ret VALUES (@d )
---print @d
Begin
set @d=''
end
end
end
end
RETURN
end
--wywolanie jn.
--Select *, (Select* FROM dbo.fnGetNumbersDecStringNew(c4,4))as kl From tblSubstring
--select* FROM dbo.fnGetNumbersDecStringNew('w10w10d20f35',3)
--Select (Select* FROM dbo.fnGetNumbersDecStringNew(c4,1))+(Select* FROM dbo.fnGetNumbersDecStringNew(c4,2))as SumaC1iC2 From tblSubstring where c1=10
GO
Poz JB