Unanswered: How To Extract Data From A Concatenated Field
Hi everyone. I need help extracting information from a concatenated field delimited by a '/' character. I know that the logic is to basically find the position of the '/' character and use the substring function to extract the data but I am unfamiliar with how to do this in SQL Server 2000. Please see examples below. Thanks in advance.
I need to extract the data before and after the '/' character. There are records however with on the leftmost part of the data such as row number 3.
FIELD 1 FIELD 2
I'll move this thread to the Microsoft SQL forum, since it requires string handling that is nearly impossible to do using generic SQL.
One solution would be:
CREATE TABLE #patp (
FIELD VARCHAR(20) NOT NULL
INSERT INTO #patp (FIELD)
UNION ALL SELECT '2345/6754'
UNION ALL SELECT '1234'
, CASE WHEN 0 = p THEN FIELD ELSE Left(FIELD, p - 1) END AS left_side
, CASE WHEN 0 = p THEN '' ELSE Stuff(FIELD, 1, p, '') END AS right_side
FROM (SELECT FIELD, CharIndex('/', FIELD) AS p
FROM #patp) AS a
DROP TABLE #patp
,substring(FIELD,1,CharIndex('/', FIELD+'/')-1) AS left_side
,substring(FIELD,CharIndex('/', FIELD+'/')+1,len(FIELD)) AS right_side
,Left(FIELD, CharIndex('/', FIELD+'/') - 1) AS left_side
,Stuff(FIELD, 1, CharIndex('/', FIELD+'/'), '') AS right_side