Convert space seperated values of a column to single column

Question

I have a column with values as mentioned below.

Transaction_ID
GT980 FR563
BR923 DE498 QS347
HB743

How can I convert and bring the results like below. I need to split the value with space delimiter. Any suggestions on how this can be achieved.

 Transaction_ID
   GT980 
   FR563
   BR923 
   DE498 
   QS347
   HB743

Sql-server `<>` MySQL. Which one are you using? Also, splitting strings is a very common task. What issues are you having with the solutions found in the archives? — SOS, Mar 15 '22 at 07:35
@Learner - SSMS is an IDE. We mean what version of SQL Server. `SELECT @@version`. SQL Server 2017+ supports [STRING_SPLIT()](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15). Lots of examples in the archives too https://stackoverflow.com/search?q=%5Bsql-server%5D+string_split — SOS, Mar 15 '22 at 07:37
Did you search the archives? This question comes up ... a lot. — SOS, Mar 15 '22 at 07:45

score 0 · Accepted Answer · edited Mar 15 '22 at 08:16

0

Create a string split function in SQLl.

CREATE FUNCTION fnSplitString
( 
  @string VARCHAR(MAX), 
  @delimiter VARCHAR(MAX) 
) 
RETURNS @output TABLE(splitdata VARCHAR(MAX)) 
BEGIN 

  DECLARE @Xml XML

  SET @Xml = CAST(('<a>'+REPLACE(@string,@delimiter,'</a><a>')+'</a>') AS XML)

  INSERT INTO @output (splitdata)
    SELECT ltrim(rtrim(A.value('.', 'VARCHAR(MAX)')))  FROM @Xml.nodes('a') AS FN(a)

  RETURN 
END

and then use it as

select cs.* 
from yourtable
cross apply fnSplitString (Transaction_ID, ' ') cs

edited Mar 15 '22 at 08:16

Dale K

25,246
15
42
71

answered Mar 15 '22 at 07:45

shotgun02

756
4
14

Multi-line table value functions are well known to perform poorly. An inline table value function would be far better, or just use the inbuilt function, `STRING_SPLIT`. Either would be much faster. – Thom A Mar 15 '22 at 09:02

Convert space seperated values of a column to single column

1 Answers1