SQL case function to check for charindex as '' or ','
-
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[^]
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
-
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
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 TableNameThe need to optimize rises from a bad design.My articles[^]
-
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 TableNameThe need to optimize rises from a bad design.My articles[^]
-
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 TableNameThe need to optimize rises from a bad design.My articles[^]
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 TableNameKeep It Simple Stupid! (KISS)
-
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[^]
-
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 TableNameKeep It Simple Stupid! (KISS)
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[^]
-
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[^]
-
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[^]
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 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[^]
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
-
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 TableNameKeep It Simple Stupid! (KISS)
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
-
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
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 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[^]
Hey Mika, I got it working.Thanks for your help......again :). Lots of Bestwishes, Jiju
-
Hey Mika, I got it working.Thanks for your help......again :). Lots of Bestwishes, Jiju