split string into two columns

• jkw

  SSC Eights!

  Points: 815

  More actions

  August 10, 2018 at 11:48 am

  #375195

  I'm looking to split a string into 2 columns. The first column will have the first 4 characters of the string. The second column will contain the rest of the string.

  HOWEVER, if the 5th character in the string is a hyphen then I want that hyphen removed. Any other hyphens after the 5th character can remain.

  1240-60-018-2            1240     60-018-2
  1240-60-1850            1240    60-1850
  1240-651-140            1240    651-140
  1285007620591            1285    007620591    
  1670000886764            1670    000886764

• ScottPletcher

  SSC Guru

  Points: 101103

  More actions

  August 10, 2018 at 11:53 am

  #2001290

  jon.wilson - Friday, August 10, 2018 11:48 AM

  I'm looking to split a string into 2 columns. The first column will have the first 4 characters of the string. The second column will contain the rest of the string.

  HOWEVER, if the 5th character in the string is a hyphen then I want that hyphen removed. Any other hyphens after the 5th character can remain.

  1240-60-018-2            1240     60-018-2
  1240-60-1850            1240    60-1850
  1240-651-140            1240    651-140
  1285007620591            1285    007620591    
  1670000886764            1670    000886764

  SELECT LEFT(columnname, 4) AS column1, SUBSTRING(columnname, CASE WHEN SUBSTRING(columnname, 5, 1) = '-' THEN 6 ELSE 5 END, 1000) AS column2

  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".

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 10, 2018 at 11:57 am

  #2001291

  here you go, here is one way:
  i just used a case statement to examine the fifth character, to determine where to start the substring:
  /*
ActualValue  ExpectLeftValue  ExpectedRightValue  ActualRightValue  ActualRightValue
1240-60-018-2 1240     60-018-2     1240      60-018-2
1240-60-1850 1240     60-1850     1240      60-1850
1240-651-140 1240     651-140     1240      651-140
1285007620591 1285     007620591     1285      007620591
1670000886764 1670     000886764     1670     000886764
*/
;WITH MyCTE([ActualValue],[ExpectLeftValue],[ExpectedRightValue])
AS
(
SELECT '1240-60-018-2','1240','60-018-2' UNION ALL
SELECT '1240-60-1850','1240','60-1850' UNION ALL
SELECT '1240-651-140','1240','651-140' UNION ALL
SELECT '1285007620591','1285','007620591' UNION ALL
SELECT '1670000886764','1670','000886764'
)
SELECT MyCTE.*,
LEFT([ActualValue],4) AS ActualRightValue,
CASE WHEN SUBSTRING([ActualValue],5,1)='-' THEN SUBSTRING([ActualValue],6,30)
ELSE SUBSTRING([ActualValue],5,30)
END AS ActualRightValue

  FROM MyCTE;

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• jkw

  SSC Eights!

  Points: 815

  More actions

  August 10, 2018 at 11:57 am

  #2001292

  That's what I needed - Thanks

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  August 11, 2018 at 2:39 pm

  #2001377

  Lowell - Friday, August 10, 2018 11:57 AM

  here you go, here is one way:
  i just used a case statement to examine the fifth character, to determine where to start the substring:
  /*
  ActualValue  ExpectLeftValue  ExpectedRightValue  ActualRightValue  ActualRightValue
  1240-60-018-2 1240     60-018-2     1240      60-018-2
  1240-60-1850 1240     60-1850     1240      60-1850
  1240-651-140 1240     651-140     1240      651-140
  1285007620591 1285     007620591     1285      007620591
  1670000886764 1670     000886764     1670     000886764
  */
  ;WITH MyCTE([ActualValue],[ExpectLeftValue],[ExpectedRightValue])
  AS
  (
  SELECT '1240-60-018-2','1240','60-018-2' UNION ALL
  SELECT '1240-60-1850','1240','60-1850' UNION ALL
  SELECT '1240-651-140','1240','651-140' UNION ALL
  SELECT '1285007620591','1285','007620591' UNION ALL
  SELECT '1670000886764','1670','000886764'
  )
  SELECT MyCTE.*,
  LEFT([ActualValue],4) AS ActualRightValue,
  CASE WHEN SUBSTRING([ActualValue],5,1)='-' THEN SUBSTRING([ActualValue],6,30)
  ELSE SUBSTRING([ActualValue],5,30)
  END AS ActualRightValue

  FROM MyCTE;

  Instead of hard coding as 30 I think we can use  len([ActualValue]) to give correct results if the values are changing .

  Saravanan