Spis treści:

Kategoria:SQL Server


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:

--Utworzenie funkcji
CREATE FUNCTION dbo.GetDigits(@str nvarchar(MAX)) RETURNS nvarchar(MAXAS
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:

CREATE FUNCTION dbo.GetNumbers(@str nvarchar(MAX))
RETURNS @ret TABLE(value intAS
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
SELECTFROM dbo.GetNumbers('F145-D12'--145, 12
SELECTFROM dbo.GetNumbers('123.456.789'--123, 456, 789
SELECTFROM 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

, 2013-12-20

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
bardo ciekawe , można dzięki takim wpisom dostrzec wędkę..
Bardzo dziękuję za jasne tłumaczenie z dobrze dobranym przykładem!
Dzieki za te informacje. były kluczowe
Dobrze wyjaśnione, dzięki !