>>>
select pnrid
>>> ,[SEGMENTTATTOONUMBERS]
>>> ,[PAXTATTOONUMBERS]
>>>from tst
>>>where not exists (select pnrid
>>> ,associatedsegmenttattoonumbers
>>> ,associatedpaxtattoonumbers
>>> from ticket
>>> where pnrid = tst.pnrid
>>> and associatedsegmenttattoonumbers = segmenttattoonumbers
>>> and associatedpaxtattoonumbers = paxtattoonumbers)
>>> order by pnrid
>>>
>>>How would I adjust this to handle the splitting up of the PaxTattooNumbers?
>>
>>If you use SQL Server 2016 :-) there is biult-in function STRING_SPLIT.
>>For smaller version google TSQL SPLIT :-)
>
>I am on SQL 2012 so can't use the suggested built in function. From what I am seeing, I need to write (or copy one that was written already) a tsql function which will split it up.
>
>I'm not seeing how I would then apply that to my problem, can you help fill in the blank for me?
>
>Say I have a function:
>
>
>CREATE FUNCTION [dbo].[fnSplitString]
>(
> @string NVARCHAR(MAX),
> @delimiter CHAR(1)
>)
>RETURNS @output TABLE(splitdata NVARCHAR(MAX)
>)
>BEGIN
> DECLARE @start INT, @end INT
> SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
> WHILE @start < LEN(@string) + 1 BEGIN
> IF @end = 0
> SET @end = LEN(@string) + 1
>
> INSERT INTO @output (splitdata)
> VALUES(SUBSTRING(@string, @start, @end - @start))
> SET @start = @end + 1
> SET @end = CHARINDEX(@delimiter, @string, @start)
>
> END
> RETURN
>END
>
>
>How would I adjust my query to get this to work?
Something like:
....
EXISTS(
select pnrid
,associatedsegmenttattoonumbers
,associatedpaxtattoonumbers
from ticket
where pnrid = tst.pnrid
and associatedsegmenttattoonumbers = segmenttattoonumbers
and associatedpaxtattoonumbers IN (SELECT Splitdata
FROM dbo.fnSplitString(paxtattoonumbers, ',') Test)
)
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.