How to retrieve two sets of values from one column

  • I need help to accomplish the task of getting two values from the string in one column.

    I need to extract the first set of characters as regardless of the length until the first forward slash (/) is found.

    The second task I need to do is to get the value after the first forward slash until the second back slash is found. (Row IDs 1 and 2 meet this criteria)

    For row number 3, there is a forward slash but, it is not in the whole value is not in the same format as the first two rows. In this case I need to ignore this row.

    This is the values I need to see based on the sample date

    Value1 Value2

    357-1329 user one (row 1)

    224-57 User Two (row 2)

    This is the DDL

    Drop table MyTestTable

    CREATE TABLE [dbo].[MyTestTable](

    [ID] int Not NULL,

    [Col1] [varchar](100) NULL)

    Insert into MyTestTable

    (ID, Col1)

    Select 1, '357-1329 / user one / User One/357-1329' UNION ALL

    Select 2, '224-57 / User Two / O0427A-85311 EG 8/24' UNION ALL

    Select 3, '214-962 User Three PLUMBING SUPPLIES EG 8/30'

    Thanks in advance

  • -- one more test data row

    INSERT INTO MyTestTable (ID, Col1) SELECT 4, 'a longer string value1 / a much much much much longer string value2 / whatever / whatever / whatever'

    SELECT

    Col1,

    LEFT(Col1, position_of_first_slash - 1) AS Value1,

    SUBSTRING(Col1, position_of_first_slash + 1, CHARINDEX('/', Col1, position_of_first_slash + 1) - position_of_first_slash - 1) AS Value2

    FROM dbo.MyTestTable

    CROSS APPLY (

    SELECT CHARINDEX('/', Col1) AS position_of_first_slash

    ) AS ca1

    WHERE

    Col1 LIKE '%/%/%'

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Thank you, this works.

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

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