Clever Geek Handbook

How to change a row in SQL Server

I have a string in SQL stored in a @result

type variable Varchar(MAX)

like this:

Attributes >> Items >> Category >> Package

How can I get the opposite of this without affecting the performance of the stored procedure. I want to split a string based on >>

.

Package >> Category >> Items >> Attributes
+3


source to share


3 answers


If there are at most four items in a row and none of them contain periods, you can use PARSENAME()

.

select (parsename(replace(@result, ' >> ', '.'), 1) + ' >> ' +
        parsename(replace(@result, ' >> ', '.'), 2) + ' >> ' +
        parsename(replace(@result, ' >> ', '.'), 3) + ' >> ' +
        parsename(replace(@result, ' >> ', '.'), 4)
       )


Another option is to split the string and restore it.

+3


source


Split the row into different parts using a delimiter and >>

assign row_number to each row.

Then Convert the rows into single string

, limited >>

by the description order .

This should work if you have more than 4 items



DECLARE @output VARCHAR(5000)='',
        @String VARCHAR(5000)='Attributes >> Items >> Category >> Package'

SELECT @output += splt_data + ' >> '
FROM  (SELECT Row_number()
                OVER(
                  ORDER BY (SELECT NULL))                       rn,
              Rtrim(Ltrim(Split.bb.value('.', 'VARCHAR(100)'))) splt_data
       FROM   (SELECT Cast ('<M>' + Replace(@String, '>>', '</M><M>')
                            + '</M>' AS XML) AS Data) AS A
              CROSS APPLY Data.nodes ('/M') AS Split(bb)) ou
ORDER  BY rn DESC

OUTPUT:

SELECT LEFT(@output, Len(@output) - 3) --Package >> Category >> Items >> Attributes
+1


source


If you are using SQL Server 2008 or newer, try:

    Select Reverse(@result)
-3


source






More articles:


All Articles