We help IT Professionals succeed at work.

Using substring to remove characters

I am currently using the following to strip away any characters in a field that precede a ",", however, this code returns the entire field if there is no comma...For instance if the field
had jones,BA I want to return just BA. IF the field just contained Jones than Jones would be returned. I want null or empty to be returned

substring(lastname,charindex(',',lastname)+1,len(lastname)) as LASTNAME,lastname

Thanks....
Comment
Watch Question

Commented:
case when charindex(',',lastname) = 0
  then lastname
  else substring(lastname,charindex(',',lastname)+1,len(lastname))
 end  as LASTNAME
Top Expert 2011

Commented:
How about:

select LEFT(lastname, CHARINDEX(',', lastname) - 1)  as lastname from your table WHERE CHARINDEX(',', lastname) > 0

Author

Commented:
Worked great..Thank you very much....Now I want to nest this case within a MAX(CASE WHEN VALUE = 5250 THEN LASTNAME END)AS NAME
Top Expert 2011

Commented:
Oops, should be RIGHT, not left --sorry about that.
select RIGHT(lastname, CHARINDEX(',', lastname) - 1)  as lastname from your table WHERE CHARINDEX(',', lastname) > 0 

Open in new window

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
MAX(CASE WHEN VALUE = 5250 THEN SUBSTRING(lastname, CHARINDEX(',', lastname) + 1,
CASE WHEN CHARINDEX(',', lastname) = 0 THEN 0 ELSE 500 END) END)

Commented:
MAX(CASE WHEN VALUE = 5250
THEN
case when charindex(',',lastname) = 0
  then lastname
  else substring(lastname,charindex(',',lastname)+1,len(lastname))
 end
END) AS NAME
Commented:
Adding an ELSE will be better
MAX(CASE WHEN VALUE = 5250 
THEN 
case when charindex(',',lastname) = 0 
  then lastname 
  else substring(lastname,charindex(',',lastname)+1,len(lastname)) 
 end 
ELSE null
END) AS NAME

Open in new window

CERTIFIED EXPERT
Top Expert 2011

Commented:
nullif(max(case [value] when 5350 then stuff(lastname,1,case charindex(',',lastname) when 0 then len(lastname) else charindex(',',lastname) end,'') end),'') as Name

Author

Commented:
Thanks to everyone..I got it to work....

Explore More ContentExplore courses, solutions, and other research materials related to this topic.