Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Splitting a string from a column into three values Expand / Collapse
Author
Message
Posted Wednesday, February 27, 2013 9:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 9:33 AM
Points: 36, Visits: 307
Hi, this is my DDL and sample data for my question.
CREATE TABLE MytestTable(
ID [int],
SplitValue [varchar](40) NULL,
)

Insert into MytestTable
(ID, SplitValue)
SELECT 1, 'DM5FCL-18461-PLANT MANAGEMENT' UNION ALL
SELECT 2, 'OMDPHR-92911-Test Value' UNION ALL
SELECT 3, 'OMMHIM-93611-MEDICAL RECORDS' UNION ALL
SELECT 4, 'S406-20330-Test Services' UNION ALL
SELECT 5, 'SSCN-20118-Network Services'

I need help in writing a query to separate the string in the sample rows into three columns separating them when the dash (-) is found.
For example, the value in row number 1, I need to display the following,

Value1, Value2, Value3
DM5FCL 18461 PLANT MANAGEMENT

I will appreciate any help on this.
Thanks
Post #1424605
Posted Wednesday, February 27, 2013 10:22 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:11 PM
Points: 31,368, Visits: 15,837
You can use PATINDEX or CHARINDEX to do this. Here's a sample: http://www.sqlservercentral.com/articles/String+Manipulation/94365/






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1424639
Posted Wednesday, February 27, 2013 11:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 12,962, Visits: 32,501
great jo providing the setup data!

Here's another fast way using the PARSENAME function;
PARSENAME is usually used to split object names, like ServerName.Databasename.dbo.TableName,
but can be used for IP address and other strings, and is limited to 4 parts.
it also thinks the strings are right to left(4,3,2,1), where we would see the string as left to right (1,2,3,4)


note that this assumes your data will not have periods in it,
and is all three parts like your example data.
select 
PARSENAME(REPLACE(SplitValue,'-','.'),3) AS PartT1,
PARSENAME(REPLACE(SplitValue,'-','.'),2) AS PartT2,
PARSENAME(REPLACE(SplitValue,'-','.'),1) AS PartT3,
MytestTable.*
FROM MytestTable



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1424657
Posted Wednesday, February 27, 2013 11:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 9:33 AM
Points: 36, Visits: 307
Thank you, this last solution works.
Post #1424663
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse