Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2005
    Posts
    91

    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.


    FIELD
    ----------
    1234/4567
    2345/6754
    1234


    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
    ------------------
    1234 4567
    2345 6754
    1234

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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:
    Code:
    CREATE TABLE #patp (
       FIELD	VARCHAR(20)	NOT NULL
       )
    
    INSERT INTO #patp (FIELD)
       SELECT '1234/4567'
       UNION ALL SELECT '2345/6754'
       UNION ALL SELECT '1234'
    
    SELECT FIELD
    ,  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
    -PatP

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Other solutions
    Code:
    select FIELD
    ,substring(FIELD,1,CharIndex('/', FIELD+'/')-1) AS left_side
    ,substring(FIELD,CharIndex('/', FIELD+'/')+1,len(FIELD)) AS right_side
    FROM #patp
    
    select FIELD
    ,Left(FIELD, CharIndex('/', FIELD+'/') - 1) AS left_side
    ,Stuff(FIELD, 1, CharIndex('/', FIELD+'/'), '') AS right_side
    FROM #patp

  4. #4
    Join Date
    Nov 2005
    Posts
    91
    Thanks for the help! This is exactly what I was ooking for.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •