SQL - Separate Out Text From String

Question

I have a table (in SQL Server) that has 2 columns. One column contains multiple server names separated with a tilda ~. This column could or could not contain the actual server name. The other column has the actual server name.

I am looking for a way to separate the first column values into their own separated column. The number of aliases range from 1 to ?.

Server_Alias_Names                              Actual_Server_Name
------------------------------------------------------------
Server1~ROSCO24~Server3~Server4~~~~~            ROSCO24
STEVETESDB26~~~~~~~~~                           STEVETESDB26
RALPHPRD117~RALPHPRD117-adm~Server0025~Server0025a1~Server0025a2~Server0025a3~~~~~        RALPHPRD117
Server1001~Server1001R1~Server1001-adm~~~~~~~   DBTEST1001

I have the first two servers extracted from the string, I am having trouble on the next few. Any help is appreciated!!!

SELECT
    LEFT(Server_Alias_Names, CHARINDEX('~', Server_Alias_Names) - 1) as 'First_Server',
SUBSTRING(Server_Alias_Names,len(LEFT(Server_Alias_Names, CHARINDEX('~', Server_Alias_Names)+1)),LEN(LEFT(Server_Alias_Names, CHARINDEX ('~', Server_Alias_Names)))) as 'Second_Server'
FROM 
    TBL_NAME

Sean Lange · Accepted Answer · 2016-03-09 21:51:59Z

3

The easiest way to accomplish this is with a string splitter. Aaron Bertrand has fairly complete list of viable options here. http://sqlperformance.com/2012/07/t-sql-queries/split-strings. Notice that none of these have any loops or recursion. I am not quite sure what you are trying to do with this information but you stated you just wanted to parse the strings.

answered Mar 9, 2016 at 21:51

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hogan · Accepted Answer · 2016-03-11 19:36:35Z

1

WITH splitit
AS
(
    SELECT Actual_Server_Name,  len(Actual_Server_Name) - len(replace(Actual_Server_Name, '~', '')) as TildaCount,
    CONVERT(XML,'<X><x>'  
    + REPLACE(Server_Alias_Names,'~', '</x><x>') + '</x></X>') AS xmlname
      FROM table
)
SELECT Actual_Server_Name,      
       CASE TildaCount > 0 THEN xmlname.value('/X/x[1]','varchar(100)') ELSE '' END AS s1,    
       CASE TildaCount > 1 THEN xmlname.value('/X/x[2]','varchar(100)') ELSE '' END AS s2,    
       CASE TildaCount > 2 THEN xmlname.value('/X/x[3]','varchar(100)') ELSE '' END AS s3,    
       CASE TildaCount > 3 THEN xmlname.value('/X/x[4]','varchar(100)') ELSE '' END AS s4,    
       CASE TildaCount > 4 THEN xmlname.value('/X/x[5]','varchar(100)') ELSE '' END AS s5,    
       CASE TildaCount > 5 THEN xmlname.value('/X/x[6]','varchar(100)') ELSE '' END AS s6,    
       CASE TildaCount > 6 THEN xmlname.value('/X/x[7]','varchar(100)') ELSE '' END AS s7
FROM splitit

You can also remove extra ~ from the end if you want, but I think it will work even if you don't.

edited Mar 11, 2016 at 19:36

answered Mar 9, 2016 at 22:17

Hogan

70.7k10 gold badges82 silver badges119 bronze badges

2 Comments

Jeremy F. Over a year ago

If I ran your code I get the following error: Msg 2389, Level 16, State 1, Line 11 XQuery [splitit.xmlname.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

Hogan Over a year ago

@JeremyF. that means you have some items with less than 7 elements -- I will write some example code of how to solve... but it won't be tested...

Jeremy F. · Accepted Answer · 2016-03-11 18:30:43Z

0

@Hogan - I ended up combining your script with some help from the link that @Sean Lange put in his comment. Here is what I came up with.

WITH splitit
AS
(
SELECT 
y.i.value('(./text())[1]', 'nvarchar(4000)') as Separated_Server,
Actual_Server_Name
FROM 
          ( 
            SELECT x = CONVERT(XML, '<i>' 
            + REPLACE(Server_Alias_Names,'~', '</i><i>') 
            + '</i>').query('.')
            , Actual_Server_Name
            FROM TBL_NAME
          ) as a cross apply x.nodes('i') as y(i)
)

SELECT DISTINCT
Actual_Server_Name,
Separated_Server
FROM splitit
Where Separated_Server is not null
ORDER BY 1,2

--Some of the separated items were also sorted by a comma so I added another step to separate those as well.
--Uncomment the code below for an additional and replace the comma after Separated_Server with the special character you want to use for separation

--,splitit_2
--AS
--(
--SELECT distinct
--ServerName,
--y.i.value('(./text())[1]', 'nvarchar(4000)') as Server_Alias

--FROM 
--          ( 
--          SELECT x = CONVERT(XML, '<i>' 
--          + REPLACE(Separated_Server,',', '</i><i>') 
--          + '</i>').query('.')
--          ,[ServerName]
--          FROM splitit
--        ) as a cross apply x.nodes('i') as y(i)
--)


--SELECT DISTINCT
--Actual_Server_Name,
--Separated_Server
--FROM splitit_2
--Where Separated_Server is not null
--ORDER BY 1,2

answered Mar 11, 2016 at 18:30

Jeremy F.

1,89014 gold badges57 silver badges90 bronze badges

1 Comment

Hogan Over a year ago

This is totally different than what you asked for -- what you asked for is a result that has 7 columns. Wish I knew this is what you wanted... much easier.

Collectives™ on Stack Overflow

SQL - Separate Out Text From String

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related