SQL case function to check for charindex as '' or ','

Trustapple

Hello Friends, I wrote a query for a purpose like this;I have a field by name "CustomerName".I want it to be split as FirstName and LastName as shown below: CustomerName FirstName LastName Jack Daniels Jack Daniels Jack,Daniels Jack Daniels My query looks like this; select substring(customername,1,CHARINDEX(',', customername) -1) as [firstname ], SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername)) AS [LastName] from TableName My Problem is i have two conditions the splitter could be "," or "" in the customername field I need to integrate this conditions to my query either using "case" or "iif". ie if the charindex is " " do this and if the charindex is ", " do this I am not sure how i can do this.Any help is welcome

Wendelius

Use CASE[^] and test if CHARINDEX for comma or space is greater than 0 and based on that use SUBSTRING to split the string. Something like:

... CASE
WHEN CHARINDEX(',', customername) > 0 THEN SUBSTRING(customername,1, CHARINDEX(',', customername) -1)
ELSE SUBSTRING(customername,1,CHARINDEX(' ', customername) -1)
END AS FirstName ....

The need to optimize rises from a bad design.My articles[^]

Trustapple

Hi Mika, I modified my query like this;It still is not working :( select customername, case when CHARINDEX(',', customername)>0 then substring(customername,1,CHARINDEX(',', customername) -1) as [firstname ], SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername)) AS [LastName] else substring(customername,1,CHARINDEX(',', customername) -1) as [firstname ], SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername)) AS [LastName] from TableName

Wendelius

Trustapple wrote:

It still is not working

Getting and error message? I think it should be more like (may contain several typos):

select customername,
case
when CHARINDEX(',', customername)>0 then
substring(customername,1,CHARINDEX(',', customername) -1)
else
substring(customername,1,CHARINDEX(' ', customername) -1)
end as [firstname ],
case
when CHARINDEX(',', customername)>0 then
SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername))
else
SUBSTRING(customername, CHARINDEX(' ', customername) + 1, LEN(customername))
end AS [LastName]
from TableName

The need to optimize rises from a bad design.My articles[^]

Trustapple

Hey Mika, Thanks for you trying to help.My server i unavailable now.I will be able to access it only after 5hours.As soon as i try thi s query i will post a reply and let you know. Many Thanks for your help....:) Merry Christmas

Ben Fair

I think it should be:

select customername,
-- get the last name
case
when CHARINDEX(',', customername) > 0 then
SUBSTRING(customername, 1, CHARINDEX(',', customername) - 1) -- comma is delimiter
when CHARINDEX(' ', customername) > 0 then
SUBSTRING(customername, 1, CHARINDEX(' ', customername) - 1) -- space is delimiter
else
'' -- neither comma nor space found
end as [lastname],
-- get the first name
case
when CHARINDEX(',', customername) > 0 then
SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername) - CHARINDEX(',', customername) + 1) -- comma is delimiter
when CHARINDEX(' ', customername) > 0 then
SUBSTRING(customername, CHARINDEX(' ', customername) + 1, LEN(customername) - CHARINDEX(' ', customername) + 1) -- space is delimiter
else
'' -- neither comma nor space found
end AS [firstname]
from TableName

Keep It Simple Stupid! (KISS)

Wendelius

Trustapple wrote:

Thanks for you trying to help

No problem :)

Trustapple wrote:

I will be able to access it only after 5hours.As soon as i try thi s query i will post a reply and let you know

At that time it's 3:30 AM here so I'll be :zzz: :zzz: :zzz: But I'll have a look at your reply in the morning. If you encounter problems I believe that someone else will help you forward in the meantime.

Trustapple wrote:

Merry Christmas

Merry Christmas to you too :)

The need to optimize rises from a bad design.My articles[^]

Wendelius

Yeah, you're rught. I misplaced the commas. Corrected now.

Ben Fair wrote:

SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername) - CHARINDEX(',', customername) + 1) -- comma is delimiter

Although it's 'cleaner' to use the rest of the length as a parameter, it's not necessary. Basically if you want the rest of the string you can pass any value as long as it's at least the rest of the length.

The need to optimize rises from a bad design.My articles[^]

Ben Fair

Oh, I wasn't aware of that; I'll have to remember that! Thanks!

Keep It Simple Stupid! (KISS)

Trustapple

Hey Mika, Thanks again to you and Ben....It worked.I made slight modification in the query though...a very slight one. The credit goes to you guys .... Please see the working query and let me know if any tweaking is necessary...becuase i will be cghnaging this select to update query for around 9million records.....:) select customername, -- get the last name case when CHARINDEX(',', customername) > 0 then SUBSTRING(customername, 1, CHARINDEX(',', customername) - 1) -- comma is delimiter when CHARINDEX(' ', customername) > 0 then SUBSTRING(customername, CHARINDEX(' ', customername) + 1, LEN(customername) - CHARINDEX(' ', customername) + 1) -- space is delimiter else '' -- neither comma nor space found end as [lastname], -- get the first name case when CHARINDEX(',', customername) > 0 then SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername) - CHARINDEX(',', customername) + 1) -- comma is delimiter when CHARINDEX(' ', customername) > 0 then SUBSTRING(customername, 1, CHARINDEX(' ', customername) - 1) -- space is delimiter else '' -- neither comma nor space found end AS [firstname] from TableName

Trustapple

Hey Mika, The time now is 7:59 AM for you,i beleive....Happy sleeping:). Thanks again to you and Ben....It worked.I made slight modification in the query though...a very slight one. The credit goes to you guys .... Please see the working query and let me know if any tweaking is necessary...becuase i will be cghnaging this select to update query for around 9million records.....:) select customername, -- get the last name case when CHARINDEX(',', customername) > 0 then SUBSTRING(customername, 1, CHARINDEX(',', customername) - 1) -- comma is delimiter when CHARINDEX(' ', customername) > 0 then SUBSTRING(customername, CHARINDEX(' ', customername) + 1, LEN(customername) - CHARINDEX(' ', customername) + 1) -- space is delimiter else '' -- neither comma nor space found end as [lastname], -- get the first name case when CHARINDEX(',', customername) > 0 then SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername) - CHARINDEX(',', customername) + 1) -- comma is delimiter when CHARINDEX(' ', customername) > 0 then SUBSTRING(customername, 1, CHARINDEX(' ', customername) - 1) -- space is delimiter else '' -- neither comma nor space found end AS [firstname] from TableName

Trustapple

Hey , Thanks a lot to you and Mika it worked. You guys have been wonderful. Hey i ran into another problem.... I might need to integrate one more condition;ie along with our query i want it to also do this that is if the customername is Mika (ie the name is only one word and it doesnot contain any ',' or ' ')Then it should update to FirstName.....Am pasting the working query below can any of you integrate this to my query..... Name FirstName LastName Mika Mika Ben Ben QUERY:update TableName set lastname= -- get the last name case when CHARINDEX(',', customername) > 0 then SUBSTRING(customername, 1, CHARINDEX(',', customername) - 1) -- comma is delimiter when CHARINDEX(' ', customername) > 0 then SUBSTRING(customername, CHARINDEX(' ', customername) + 1, LEN(customername) - CHARINDEX(' ', customername) + 1) -- space is delimiter else '' -- neither comma nor space found end, -- get the first name firstname = case when CHARINDEX(',', customername) > 0 then SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername) - CHARINDEX(',', customername) + 1) -- comma is delimiter when CHARINDEX(' ', customername) > 0 then SUBSTRING(customername, 1, CHARINDEX(' ', customername) - 1) -- space is delimiter else '' -- neither comma nor space found end

modified on Thursday, December 11, 2008 2:23 AM

Wendelius · modified on Thursday, December 11, 2008 2:23 AM

Trustapple wrote:

Thanks

You're welcome.

Trustapple wrote:

along with our query i want it to also do this that is if the customername is Mika (ie the name is only one word and it doesnot contain any ',' or ' ')Then it should update to FirstName.....

Do you mean something like this:

update TableName
set lastname
= case
when CHARINDEX(',', customername) > 0 then
SUBSTRING(customername, 1, CHARINDEX(',', customername) - 1) -- comma is delimiter
when CHARINDEX(' ', customername) > 0 then
SUBSTRING(customername, CHARINDEX(' ', customername) + 1, LEN(customername) - CHARINDEX(' ', customername) + 1) -- space is delimiter
else
TableName.LastName -- no delimitter so don't change lastname in the table
end,
firstname
= case
when CHARINDEX(',', customername) > 0 then
SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername) - CHARINDEX(',', customername) + 1) -- comma is delimiter
when CHARINDEX(' ', customername) > 0 then
SUBSTRING(customername, 1, CHARINDEX(' ', customername) - 1) -- space is delimiter
else
customername -- no delimiter so use the full data to firstname
end ...

The need to optimize rises from a bad design.My articles[^]

Trustapple

Hey Mika, I got it working.Thanks for your help......again :). Lots of Bestwishes, Jiju

Wendelius

Trustapple wrote:

Thanks for your help

You're welcome.

The need to optimize rises from a bad design.My articles[^]