returning part of string value where separator is '-'

Question

Post reply

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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]