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

How to retrieve two sets of values from one column Expand / Collapse
Author
Message
Posted Tuesday, October 29, 2013 3:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:36 PM
Points: 36, Visits: 281
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
Post #1509580
Posted Tuesday, October 29, 2013 4:03 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:27 PM
Points: 2,127, Visits: 3,216
-- 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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1509597
Posted Tuesday, October 29, 2013 4:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:36 PM
Points: 36, Visits: 281
Thank you, this works.
Post #1509603
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse