December 7, 2005 at 7:27 am
Hi People,
I'd really appreciate a pointer in the right direction! I'm trying to use CHARINDEX to get the surnames and firstnames out of a single 'name' field eg: 'Smith, Paul'
This is my statement for getting the surname:
select left(artist, charindex(',', artist)) from PRODUCTS order by artist asc
Which is fine for the example above, but when I get to 'Dana And Pepper', CHARINDEX returns 0 and the statement fails. How do I cope with the fact that the ',' may not be found in the 'name' field?
Cheers for taking the time for reading this!
regards,
Tim
December 7, 2005 at 8:01 am
I would think you could use a case statement in the SELECT clause.
SELECT CASE WHEN artist not LIKE '%,%' THEN artist ELSE left(artist, charindex(',', artist)) END
or somthing to that effect.
December 7, 2005 at 8:25 am
or
Select Case when charindex(',',Artist) = 0 then Artist else left(artist,charindex(',',artist)) end
Or run 2 queries
select left(artist,charindex(',',artist))
From Mytable
where left(artist,charindex(',',artist)) > 0
union
select artist
From Mytable
where left(artist,charindex(',',artist)) = 0
December 8, 2005 at 8:55 am
I'd prefer Ray M's first option:
Select Case when charindex(',',Artist) = 0 then Artist else left(artist,charindex(',',artist)) end
Why muddy things up with a union ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply