CODEWITHSUNDEEP
sqlsql-serversubstring
Jeremy F.
Jeremy F.

Reputation: 1868

SQL - Separate Out Text From String

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

Upvotes: 1

Views: 168

Answers (3)

Hogan
Hogan

Reputation: 70523

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.

Upvotes: 1

Jeremy F.
Jeremy F.

Reputation: 1868

@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

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33581

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.

Upvotes: 3

Related Questions