returning part of string value where separator is '-'

• luissantos

  SSCertifiable

  Points: 7391

  More actions

  July 28, 2008 at 5:24 am

  #210588

  Hello comunity

  i have this string value on my table :

  V104 - Luis

  I need to return only V104 , i mean that beginning to the left side on the first occurence that find the character '-' then i keep only all the character before .

  Thanks

  Luis Santos

• Atif-ullah Sheikh

  SSChampion

  Points: 12495

  More actions

  July 28, 2008 at 5:36 am

  #849088

  I think you are looking for CHARINDEX,

  Declare @YourVal as varchar(12)

  Set @YourVal = 'V104 - Luis'

  Select Left(@YourVal, Charindex('-',@YourVal)-1)

  I hope it will help you...

  Atif Sheikh

  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------

  Sometimes, winning is not an issue but trying.

  You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 28, 2008 at 5:38 am

  #849090

  DROP TABLE #Test

  CREATE TABLE #Test (TestString VARCHAR(30))

  INSERT INTO #Test (TestString)

  SELECT 'V104 - Luis' UNION ALL

  SELECT 'VHBG 104 - Luis' UNION ALL

  SELECT 'V1 04 - Luis' UNION ALL

  SELECT 'V1D04 - Luis' UNION ALL

  SELECT 'V1GFB04 - Luis' UNION ALL

  SELECT 'V1HKO04 - Luis' UNION ALL

  SELECT 'VAFGBBG - S104 - Luis' UNION ALL

  SELECT 'V104 - Luis' UNION ALL

  SELECT 'V1HK;.04 - Luis' UNION ALL

  SELECT 'V1GFG04 _ Luis'

  SELECT LEFT(TestString, CHARINDEX('-', TestString)-1)

  FROM #Test

  WHERE TestString LIKE '%-%'

  Note that the expression will fail with a syntax error if there isn't a '-' in the string.

  Cheers

  ChrisM

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Atif-ullah Sheikh

  SSChampion

  Points: 12495

  More actions

  July 29, 2008 at 2:08 am

  #849604

  You are absolutely right. Can use CASE as;

  Declare @YourVal as varchar(12)

  Set @YourVal = 'V1GFG04 - Luis '

  Select case Charindex('-',@YourVal) when 0 then 'what ever you want' else Left(@YourVal, Charindex('-',@YourVal)-1 ) end

  Main thing was the logic...

  Atif Sheikh

  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------

  Sometimes, winning is not an issue but trying.

  You can check my BLOG [font="Arial Black"]here[/font][/url][/right]