October 29, 2013 at 3:14 pm
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
October 29, 2013 at 4:03 pm
-- 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 29, 2013 at 4:37 pm
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