How to combine SQL String functions 2

[DCSage]

Hello.

Can anyone tell me how to properly combine Sql String Functions?

I have the following data to export and I need to remove everything after the first hyphen. Everything else is either an episode number or a description.

Program
SPECIALS-MVSP7EDDIE - ENTIRELY EDDIE...ENTIRELY GENIUS

Program
IRON RING-BRNG-08002

SELECT     CASE WHEN LEFT(Program, 6) IN ('-') THEN LTRIM(LEFT(Program, LEN(Program) - 5)) END AS UPD, Program, Network
FROM         SampleEpisodes

I have alot of records with this issue.

Please advise on how I can properly combine string functions to remove all text after the first hyphen.

 

[r937]

remove all text after the first hyphen" --

SELECT CASE WHEN CHARINDEX('-',Program) > 0
THEN LEFT(Program,CHARINDEX('-',Program)-1)
ELSE Program END



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[DCSage]

Thank you!!! This worked like a charm. What is the CharIndex?

 

[r937]

CHARINDEX is a function

do you have a copy of Books OnLine (a.k.a. "BOL")?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[SQLBill]

As Rudy says, check out CHARINDEX in the BOL for the specifics. But think of it this way, it finds the starting position (INDEX) of a specific character (CHAR) within a string. While you are looking up CHARINDEX, also look up PATINDEX as it comes in handy also.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[DCSage]

Thank You All!!!!!!!!