Post reply

Splitting a string from a column into three values

  • February 27, 2013 at 9:36 am

    #74171

    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

  • February 27, 2013 at 10:22 am

    #1591640

    You can use PATINDEX or CHARINDEX to do this. Here's a sample: http://www.sqlservercentral.com/articles/String+Manipulation/94365/

  • February 27, 2013 at 11:06 am

    #1591656

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 27, 2013 at 11:18 am

    #1591661

    Thank you, this last solution works.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply