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
Brak komentarzy - bądź pierwszy