2

I'm using SQL Server 2012 and I have a table A that has the Id and PseudoId columns and a bunch of others I am not interested in. What I am interested in, is the PseudoId especially. This PseudoId has to be in a specific format, but there is one catch: there can be multiple PseudoIds in the same row, separated by a semi-colon.

I have already figured out how to write the LIKE statement for 1 PseudoId, but I have some trouble to write the following query: retrieve all the Id's of A where all the PseudoIds (can be 1 or more) are in the correct format. So for 1, this is easy, but for more a little more tricky.

What I thought I had to was 'ungroup' my table A so that I return a new table A' where the following applies: if there is only 1 PseudoId on a row, then just return the row. Else, split the row on semi-colon and return the rows, which will all be identical but have a different PseudoId.

So for example:

ID | PseudoId  
-------------  
 1 | ABC  
 2 | DEF  
 3 | GHI;JKL  
 4 | MNO  
 5 | PQR;STU;VWX  

'Ungrouping' this should yield:

ID | PseudoId  
-------------  
 1 | ABC  
 2 | DEF  
 3 | GHI  
 3 | JKL  
 4 | MNO  
 5 | PQR  
 5 | STU  
 5 | VWX  

I would probably use a table-valued function, but not entirely sure how to accomplish this... Much appreciated.

1

You can do this with a TVF, when you migrate to SQL Server 2016 or higher you can use STRING_SPLIT.

Function source : https://stackoverflow.com/questions/10914576/t-sql-split-string

(slightly modified to use ';' instead of ',' )

CREATE FUNCTION

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(';', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(';', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

TEST DATA

CREATE TABLE test(id int, pseudoid varchar(255))
insert into test 
values(1 ,'ABC'),
(2 ,'DEF'),
(3 ,'GHI;JKL'),
(4, 'MNO'),
(5 ,'PQR;STU;VWX')

TEST

   select id , s.Name as PseudoID from
    test 
    cross apply dbo.splitstring(pseudoid) as s

RESULT

id  PseudoID 
1   ABC
2   DEF
3   GHI
3   JKL
4   MNO
5   PQR
5   STU
5   VWX
  • 1
    Aaahhh, I already had a split function which I was trying to use, but I didn't come up with using CROSS APPLY as I have never found a real use case for it... Until today! Thanks! – Christophe Loeys Dec 18 '18 at 9:22
  • No problem! Glad it helped – Randi Vertongen Dec 18 '18 at 9:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.