Split text String based on cahracter

I need to split a field that contains "size | color" called [desc2] into 2 seprate fields of Size and Color in MS SQL 2005. The size and color are sperated by' | '. I have used patindex before but cant remeber how and believe there must be a better way.

XXLarge | Black                                            

 
ubsmailAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:

select rtrim( LEFT(desc2, charindex('|',desc2 )-1)) , ltrim(RIGHT (desc2, len(desc2)- charindex('|',desc2 ) ) )
0
 
BitsqueezerCommented:
Hi,

this one tests if the string contains this pattern before trying to convert it.

Cheers,

Christian

SELECT CASE WHEN PATINDEX('% | %',MyColumn) = 0 THEN '' ELSE  LEFT(MyColumn,              PATINDEX('% | %',MyColumn)-1) END AS [Size],
	   CASE WHEN PATINDEX('% | %',MyColumn) = 0 THEN '' ELSE RIGHT(MyColumn,LEN(MyColumn)-PATINDEX('% | %',MyColumn)-2) END AS [Color]

Open in new window

0
 
ubsmailAuthor Commented:
select rtrim( LEFT(desc2, charindex('|',desc2 )-1)) , ltrim(RIGHT (desc2, len(desc2)- charindex('|',desc2 ) ) )

This solution left the color column blank.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
ubsmailAuthor Commented:
SELECT CASE WHEN PATINDEX('% | %',MyColumn) = 0 THEN '' ELSE  LEFT(MyColumn,              PATINDEX('% | %',MyColumn)-1) END AS [Size],
         CASE WHEN PATINDEX('% | %',MyColumn) = 0 THEN '' ELSE RIGHT(MyColumn,LEN(MyColumn)-PATINDEX('% | %',MyColumn)-2) END AS [Color]
 


This solution did the same.

the field is desc2 and the data is always size | color
0
 
ubsmailAuthor Commented:
I believe I know the issue but I am unclear how to fix it. the DESC2 is 70 cahrters so if we are using the RIGHT syntex the proper position would be 63 making it 70 -' | '  
0
 
ubsmailAuthor Commented:
select rtrim( LEFT(desc2, charindex('|',desc2 )-1)) as Size, rtrim(RIGHT(desc2, 60-charindex('|',desc2)-1)) as Color

This Worked thankyou
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.