String searching output tranformated into rows

Cześć,

mam taki temat: jak z poniższej Actual Table dojść do Desired Output 1 i Desired Output 2.
Czy korzystając z PATINDEX można znaleźć kolejne wystąpienia?
Szukany ciąg znaków to zawsze '%[0-9][0-9].[0-9][0-9].[0-9][0-9]%'

Będę bardzo wdzięczny za każdą wskazówkę:).
Pozdrawiam,
Arek

.............

Actutal Table
Ticket Text
1 dsdsd ds545 dssd54 5dsd sd sd 22.44.62 dsfdsf fsfdvdfd 11.55.77 fdfd dsf4 544f 4fd 86.22.44

Desired Output 1
Ticket Text
1 22.44.62
1 11.55.77
1 86.22.44

Desired Output 2
Ticket Text
1 22.44.62 / 11.55.77 / 86.22.44

PATINDEX to dobry trop możesz napisać funkcje:

CREATE  FUNCTION [dbo].[parseTicketText]
				(@text varchar(200))
RETURNS varchar(200)
AS
BEGIN

declare @result varchar(200)
set @result = ''

while  patindex('%[0-9][0-9].[0-9][0-9].[0-9][0-9]%',@text)>0
begin 
    set @result = @result + ' / ' + substring(@text,patindex('%[0-9][0-9].[0-9][0-9].[0-9][0-9]%',@text),8)
    set @text = STUFF(@text, patindex('%[0-9][0-9].[0-9][0-9].[0-9][0-9]%',@text), 8, '')
end

RETURN substring(@result,4,200)
END


GO

i użyc do otrzymania 2 outputu:

SELECT ticket,[dbo].[parseTicketText](TEXT) FROM TICKET

co do outputu 1 to jak masz wersje >=2016 to uzyj STRING_SPLIT https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017

Możesz też napisac funkcje:

CREATE  FUNCTION [dbo].[parseTicketTextToRows]
				(@text varchar(200))
RETURNS @RtnValue table
(
    Data varchar(8) NOT NULL
)
AS
BEGIN

while  patindex('%[0-9][0-9].[0-9][0-9].[0-9][0-9]%',@text)>0
begin 
    insert into @RtnValue values (substring(@text,patindex('%[0-9][0-9].[0-9][0-9].[0-9][0-9]%',@text),8))
    set @text = STUFF(@text, patindex('%[0-9][0-9].[0-9][0-9].[0-9][0-9]%',@text), 8, '')
end

RETURN
END


GO

i użyć w zapytaniu:

select ticket, f.data as Text from ticket 
cross apply [dbo].[parseTicketTextToRows](ticket.text) f

Dziękuję Panczo.
Będę potrzebował chwilę aby przetrawić ten kod - jestem nowy w temacie.
Mam jednak pytanie - czy w takich sytuacjach nie stosuje się pętli (np. while)?
To nie jest pożądane w SQL rozwiązanie, że nikt go nie proponuje?

Pozdrawiam,
Arek

W tych funkcjach są pętle...

Pętle nie są złe, w tym konkretnym przypadku używam ich do wyciągnięcia danych z ciągu znaków. Słabe by to było jakbyśmy używali pętli/kursorów do wyciągania danych z tabel. Zresztą za kursory w robocie obcinam
ręce ;) W swojej wieloletniej praktyce nigdy nie spotkałem się z przypadkiem by były one potrzebne... Dodam tylko, że da się to zrobić stosując rekurencję, tak że nie będzie potrzeby tworzenia dodatkowych obiektów na bazie...

Dziękuję, wszystko pięknie działa. Mam jeszcze jedno pytanie w zakresie parseTicketTextToRows (czyli Desired Output2). Jak teraz bym chciał dodać kolejną kolumnę, która wyszukuje mi coś innego z tego samego @text to muszę stworzyć drugą analogiczną funkcję i zastosować drugie cross aply jak poniżej

SELECT ident, l.DATA AS LogDate, getdate() as CreationDay
FROM Tickets
CROSS apply dbo.parseTicketTextToRows(Text) e
CROSS apply dbo.parseLogDateToRows(Text) l

czy w już istniejące funkcji można to uzyskać?

Pozdrawiam,
Arek

Trudno odpowiedzieć jak nie wiem co masz zrobić, pewnie można rozbudować funkcje, lub napisać nową.

OK, doprecyzowuję zatem. Druga funkcja szuka dokładnie w tym samym tekście daty powiązanej z numerem błędu wyszukanego w pierwszej funkcji. Założmy, że odpowiednia data jest zawsze 20 znaków przed wstapieniem numeru błędu i zawiera 10 znaków. Finalnie w drugiej kolumnie chciałbym zobaczyć dla każdego wiersza z błędem date jego wystąpienia. Widzę, że cross apply nie jest tu rozwiązaniem bo łączy wszystko ze wszystkim. Więc teraz idę albo w kierunku - funkcja tableraryczna, która zwraca dwie kolumny albo złączenie wyników obu selectów z dwoma odzielnymi funkcjami.

Wszelkie wskazówi mile widziane:)

Pozdrawiam,
Arek

Podaj przykładowy string w którym szukasz zamień to czego nie możesz pokazać ważne żeby długości się zgadzały

String jest np. taki:
CXCSDSSDSD 50 6/27/2018 2:22:52 PM 160263 10.39.53 2308937_578498 XSDS DSD dsdscsdds dsds 0 49 6/27/2018 12:14:57 PM 160236 31.03.36 2308937_578498 Mdsdsds dsds ssdsr or 48 6/26/2018 10:28:37 AM 160059 31.13.02 2 dsds sds

i chce finalnie uzyskać w dwóch oddzielnych kolumnach:
Error Time
10.39.53 6/27/2018 2:22:52 PM
31.03.36 6/27/2018 12:14:57 PM
31.13.02 6/26/2018 10:28:37 AM

Dziękuję za pomoc.
A.

Data jest około 30 znaków ponieważ jej zapis bez zer wiadących sprawia, ze długość samej daty może być od 8 (1/1/2019) do 10 (12/12/2018) znaków, jak i godzina też nie ma stałej długości, można to obejść i "wyłuskać":

ALTER  FUNCTION [dbo].[parseTicketTextToRows]
                (@text VARCHAR(500))
RETURNS @RtnValue TABLE
(
    error VARCHAR(8) NOT NULL
    ,[time] VARCHAR(30) NOT NULL
)
AS
BEGIN
declare @i as integer 
declare @error varchar(100)
declare @time varchar(300)

set @i = patindex('%[0-9][0-9].[0-9][0-9].[0-9][0-9]%',@text)

while  @i>0
BEGIN 
    SET @Error = SUBSTRING(@text,@i,8)
    set @time = substring(@text,@i-30,30)
    set @time = substring(@time,case when charindex(' ',@time)>8 then 1 else charindex(' ',@time)+1 end,patindex('% [pa]m%',@time)-case when charindex(' ',@time)>8 then 0 else charindex(' ',@time) end+3)
    INSERT INTO @RtnValue VALUES (@error,@time)
    SET @text = STUFF(@text, patindex('%[0-9][0-9].[0-9][0-9].[0-9][0-9]%',@text), 8, '')
    set @i = patindex('%[0-9][0-9].[0-9][0-9].[0-9][0-9]%',@text)
END
 
RETURN
END
 
GO

Celowo wprowadziłem zmienne, aby było czytelniej do analizy, wykorzystanie:

SELECT 
    ticket
    , f.error
    , f.[time] 
FROM 
    ticket 
    CROSS apply [dbo].[parseTicketTextToRows](ticket.text) f

Na niektórych danych działa, na niektórych mam komunikat:
Msg 537, Level 16, State 2, Line 33
Do funkcji LEFT lub SUBSTRING został przekazany parametr o nieprawidłowej długości.

Całkiem możliwe, pewnie trzeba by dopisać jakies parsowania czy w ciągu znaków jet to czego szukamy.
Bez danych nic więcej nie powiem

Dziękuję za dalszą chęć pomocy.
Przejrzałem teksy źródłowe, z ktorymi parser sobie nie radzi i problem pojawia się w sytuacjach gdy szukany error o naszej przykladowej kombinacji 11.22.33 pojawia się wcześniej w teksie i faktycznie nie ma do niego przypisanej w tym wczesniejszym miesjcu daty. Pozniej we wlasciwym miejscu jest jeszcze raz ten sam error i odnosząca się do niego data ale pojawiają się niestety takie samotne strzały wcześniej.

Nie ma tam schematu tesktowego - regula jest, że pojawia się szukany error 11.22.33 bez wczesniejszego odniesienia do daty.
Jak wylacze tę dodatkową linię parsującą to nie ma bledow, ale format jest jak zauwazyles nie do konca docelowy.

Co się praktykuje w takich sytuacjach?

Pozdrawiam,
A

--SET @TIME = SUBSTRING(@TIME,CASE WHEN charindex(' ',@TIME)>9 THEN 1 ELSE charindex(' ',@TIME)+1 END,patindex('% [pa]m%',@TIME)-CASE WHEN charindex(' ',@TIME)>9 THEN 0 ELSE

Podaj przykładowy string powodujący błąd

Wyslalem Ci wiadomość z dwoma przykladami.

Nie ma jakiegoś odpwiednik IFERROR w SQL czy nie tędy droga w tym wypadku?
A.

zmien funkcje:

ALTER  FUNCTION [dbo].[parseTicketTextToRows]
                (@text VARCHAR(500))
RETURNS @RtnValue TABLE
(
    error VARCHAR(8) NOT NULL
    ,[TIME] VARCHAR(30) NOT NULL
)
AS
BEGIN
DECLARE @i AS INTEGER 
DECLARE @error VARCHAR(100)
DECLARE @TIME VARCHAR(300)
 
SET @i = patindex('%[0-9][0-9].[0-9][0-9].[0-9][0-9]%',@text)
 
while  @i>0
BEGIN 
    SET @Error = SUBSTRING(@text,@i,8)
    SET @TIME = SUBSTRING(@text,@i-30,30)
    SET @TIME = case when  patindex('% [pa]m%',@TIME) > 8 then
	   SUBSTRING(@TIME,CASE WHEN charindex(' ',@TIME)>8 THEN 1 ELSE charindex(' ',@TIME)+1 END,patindex('% [pa]m%',@TIME)-CASE WHEN charindex(' ',@TIME)>8 THEN 0 ELSE charindex(' ',@TIME) END+3)
    else
	   ''
    end
    INSERT INTO @RtnValue VALUES (@error,@TIME)
    SET @text = STUFF(@text, patindex('%[0-9][0-9].[0-9][0-9].[0-9][0-9]%',@text), 8, '')
    SET @i = patindex('%[0-9][0-9].[0-9][0-9].[0-9][0-9]%',@text)
END
 
RETURN
END
 
GO

To co podesłałeś to xml, nie jest prawidłowy ale nie wiem czy to nie wynika z mechanizmu forum, jeżeli to faktycznie prawidłowy xml do przerobienia to prościej zamiast przerabiania go patindex uzyć funkcji xmlowych...

Dziekuję.
Tak, tekst startowy to xml o datatype = text.
Przyjrzę się zatem tym funckjom - czy Twoim zdaniem będzie to łatwiejsze rozwiazanie?

Chciałym jednak już dokończyć opcję z klasycznym wyszukiwaniem - jest już blisko oczekiwanego efektu:
nie pojawiają sie błędy jednak jako error traktowana jest zbyt duża liczba dat, co w efekcie zwraca za małą liczbę danych - przykład poniżej.
Czy można jakoś zliberalizować przypisywanie jako error?

**
Funkcja poprzednia, zły format ale dobra skuteczność:**
1322062 44.10.05 5/17/2018 1:14:25 PM
1322062 30.01.41 0 49 5/16/2018 9:11:
1322062 99.05.56 0 48 5/16/2018 9:04:
1322062 13.FF.FF 0 47 5/16/2018 2:12:
1322062 31.13.01 5/15/2018 4:46:49 AM
1322062 13.A3.D3 5 5/7/2018 5:59:06 AM
1322062 13.A3.D3 4 5/2/2018 6:20:55 AM
1322062 31.13.01 4/26/2018 12:46:48 AM
1322062 10.99.40 4/23/2018 1:41:38 PM
1322062 54.14.00 4/23/2018 4:39:05 AM
1322111 13.64.a1 Uhr Oeffnungszeiten
1322277 01.06.18 **** CASE_SUMMARY:
1322277 13.B2.D2 0 01.06.2018 11:54:20
1322277 13.B2.D2 9 01.06.2018 11:42:20
1322277 13.B2.D2 8 01.06.2018 11:41:44
1322277 13.B2.D2 7 01.06.2018 11:38:23
1322277 13.B2.D2 6 01.06.2018 11:37:33
1322277 13.B2.D2 5 01.06.2018 11:36:25
1322485 44.01.1E 50 5/4/2018 10:36:14
1322485 31.03.36 49 3/12/2018 8:47:16

**
Funkcja obecna, pięknie wyłuskuje ale dużo omija:**
1322062 44.10.05 5/17/2018 1:14:25 PM
1322062 30.01.41
1322062 99.05.56
1322062 13.FF.FF
1322062 31.13.01 5/15/2018 4:46:49 AM
1322062 13.A3.D3 5/7/2018 5:59:06 AM
1322062 13.A3.D3 5/2/2018 6:20:55 AM
1322062 31.13.01 4/26/2018 12:46:48 AM
1322062 10.99.40 4/23/2018 1:41:38 PM
1322062 54.14.00 4/23/2018 4:39:05 AM
1322111 13.64.a1
1322277 01.06.18
1322277 13.B2.D2
1322277 13.B2.D2
1322277 13.B2.D2
1322277 13.B2.D2
1322277 13.B2.D2
1322277 13.B2.D2
1322485 44.01.1E
1322485 31.03.36

Jeszcze jedna rzecz, pattern musiałem trochę zmienić (spacja przed i po), dlatego zmieniłem wyszukiwanie z 8 na 9 znaków:

ALTER FUNCTION [dbo].[fParseALLToRows5]
(@text VARCHAR(max))
RETURNS @RtnValue TABLE
(
error VARCHAR(9) NOT NULL
,[TIME] VARCHAR(30) NOT NULL
)
AS
BEGIN
DECLARE @i AS INTEGER
DECLARE @error VARCHAR(100)
DECLARE @Time VARCHAR(300)

SET @i = patindex('% [0-9][0-9].. %',@text)

while @i>0
BEGIN
SET @error = SUBSTRING(@text,@i,9)
SET @Time = SUBSTRING(@text,@i-28,21)
SET @Time = CASE WHEN patindex('% [pa]m%',@Time) > 9 THEN
SUBSTRING(@Time,CASE WHEN charindex(' ',@Time)>9 THEN 1 ELSE charindex(' ',@Time)+1 END,patindex('% [pa]m%',@Time)-CASE WHEN charindex(' ',@Time)>9 THEN 0 ELSE charindex(' ',@Time) END+3)
ELSE
''
END
INSERT INTO @RtnValue VALUES (@error,@Time)
SET @text = STUFF(@text, patindex('% [0-9][0-9].. %',@text), 9, '')
SET @i = patindex('% [0-9][0-9].. %',@text)
END

RETURN
END

Nie zrobisz tego posiłkująć się wyrażeniami dostępnymi w czystym TSQL, ja bym na twoim miejscu szedł w stronę funkcji CLR i uzył regex z prawdziwego zdarzenia.

Mam na myśli to, że tak naprawdę potrzebujesz znaleźć taki ciąg:

data w formacie d/M/yyyy AM/PM H:mm:ss + dowlny ciąg znaków + [0-9][0-9].__.__

Co można zapisać takim wyrażeniem:

\d{1,2}\/\d{1,2}\/\d{4}\s\d{1,2}:\d{1,2}:\d{2}\s[AP]M(.*?)\d\d\...\…

I dla ciągu:

CXCSDSSDSD 31.dd.dd 50 6/27/2018 2:22:52 PM 160263 10.39.53 2308937_578498 XSDS DSD dsdscsdds dsds 0 49 6/27/2018 12:14:57 PM 160236 31.03.36 2308937_578498 Mdsdsds dsds ssdsr or 48 6/26/2018 10:28:37 AM 160059 31.1A.02 2 dsds sds 31.31.31

Zwróci 3 dopasowania:

1. 6/27/2018 2:22:52 PM 160263 10.39.53
2. 6/27/2018 12:14:57 PM 160236 31.03.36
3. 6/26/2018 10:28:37 AM 160059 31.1A.02

Możesz potestować tu: regexstorm.net/tester (zrobiłem permalinka, ale jest jakis błąd firewall'a)

Inaczej zawsze będzie to łatanie, przy tak ograniczonej opcji wyrażeń w tsql.

Teraz mi się przypomniało.

Red-Gate napisał i udostepnil funkcje CLR, które wzbogacają tsql w wyrażenia reguralne z prawdziwego zdarzenia:
https://www.red-gate.com/simple-talk/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/

Pobierasz:

1. InstallRegex.sql
2. InstallRegexAssembly.sql

I puszczasz w kolejności pobrania.

Wykorzystujesz funkcje:

 select 
    ticket
    ,right(match,8) Error
    ,left(match,charindex('m',match)) Time
  from ticket
 cross apply 
  RegExMatches('\d{1,2}\/\d{1,2}\/\d{4}\s\d{1,2}:\d{1,2}:\d{2}\s[AP]M(.*?)\d\d\...\...',text,3)

I dla podanego przez ciebie 2 przykładu zwraca:

Panczo, bardzo dziękuję za pomoc. Przyjrzę się sugerowanym materiałom.
Trochę połatałem tego parsera jednak mam pełną świadomość, że na pewno wcześniej czy później przy ładowaniu kolejnych danych pojawi się cos czego nie przewidziałem i skończy się błędem.

Jeszcze tak z ciekawści - czy w takiej sytuacji istnieje jakiś sprytny sposób aby zdientyfikować, na kórym konkretnie wierszu / ciągu znaków mechanizm nie podołał?
W przeciwnym razie (tak to robie teraz) pozostaje zabawa w zawężanie zakresu do momentu aż się znajdzie jakiś niestandardowy wiersz generujący błąd - jak to zrobic sprytniej?

Pozdrawiam,
Arek