Splitting a string from a column into three values

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Splitting a string from a column into three...

Splitting a string from a column into three values

Rate Topic

Display Mode

Topic Options

Author

Message

josetur12

Posted Wednesday, February 27, 2013 9:36 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:08 PM
Points: 25, Visits: 164

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

Steve Jones - SSC Editor

Posted Wednesday, February 27, 2013 10:22 AM

SSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 1:47 PM
Points: 31,406, Visits: 13,722

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

Lowell

Posted Wednesday, February 27, 2013 11:06 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 11,605, Visits: 27,649

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

josetur12

Posted Wednesday, February 27, 2013 11:18 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:08 PM
Points: 25, Visits: 164

Thank you, this last solution works.

Post #1424663

« Prev Topic | Next Topic »

Permissions