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 Answer 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 dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
 @returnList TABLE ([Name] [nvarchar] (500))


 WHILE CHARINDEX(';', @stringToSplit) > 0
  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)

 INSERT INTO @returnList
 SELECT @stringToSplit



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')


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


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! Dec 18, 2018 at 9:22
  • No problem! Glad it helped Dec 18, 2018 at 9:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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