select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End
end
from dbo.test1Users
When i do that i get this error
Incorrect syntax near the keyword 'from'.
Thank you for your help
You need to use CASE instead of IF. Maybe something like:
Code Snippet
declare @.testUsers table
( id integer,
firstName varchar(25),
lastName varchar(25)
)
insert into @.testUsers
select 1, 'Fred', 'Rubble' union all
select 2, 'Mike', 'Flintstone' union all
select 3, 'Betty', 'Kong' union all
select 4, 'Mike', 'Kong'
--select * from @.testUsers
select id,
case when substring(firstName, 1, 4) = 'Mike'
then replace(firstName, 'Mike', 'MikeTest')
else firstName
end as firstName,
case when substring(lastName, 1, 4) = 'Kong'
then replace(lastName, 'Kong', 'KongTest')
else lastName
end as lastName
from @.testUsers
/*
id firstName lastName
- - --
1 Fred Rubble
2 MikeTest Flintstone
3 Betty KongTest
4 MikeTest KongTest
*/
Thank you for your replay
Actually I already thought about case statements but it really won’t work and here is why:
I need to have one check in top for example
If(firstName, 1, 4) = 'Mike')
Begin
replace(firstName, 'Mike', 'MikeTest')
If(LatsName, 1, 4) = 'Kong')
Begin
replace(firstName, 'Kong', 'KongTest')
End
End
The reason why ! you might have a guy his first name is “George” and his last name is “Kong”. So George’s Last name should not be changed to kongtest. Case statement won’t work because it will execute each case for example: the output from your case statements was:
id firstName lastName
- - --
1 Fred Rubble
2 MikeTest Flintstone
3 Betty KongTest
4 MikeTest KongTest
Betty’s last name should not be changed to KongTest
Thanks in advance for your Input
|||That corrected case statement looks something like this:
Code Snippet
declare @.testUsers table
( id integer,
firstName varchar(25),
lastName varchar(25)
)
insert into @.testUsers
select 1, 'Fred', 'Rubble' union all
select 2, 'Mike', 'Flintstone' union all
select 3, 'Betty', 'Kong' union all
select 4, 'Mike', 'Kong'
--select * from @.testUsers
select id,
case when substring(firstName, 1, 4) = 'Mike'
then replace(firstName, 'Mike', 'MikeTest')
else firstName
end as firstName,
case when substring(firstName, 1, 4) = 'Mike'
and substring(lastName, 1, 4) = 'Kong'
then replace(lastName, 'Kong', 'KongTest')
else lastName
end as lastName
from @.testUsers
/*
id firstName lastName
-- - --
1 Fred Rubble
2 MikeTest Flintstone
3 Betty Kong
4 MikeTest KongTest
*/
Yes this might work only for simple cases, but what I have is First Name, Last Name, Street Address, City,state,, County, Country, zip Code and more info....... Can you see my point?
Thanks
|||What was stated was a specific case; if you will give what your aim is, then I might be able to help you. Yes, I see your rhetorical point.
|||Thank you Kent.
Ok I am tying to convert a query that was written in access to Sql stored procedure or view..
Here is the part that I need to convert:
SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',
IIf(Mid([proj_name],1,9)='9026-3342','Perspective',
IIf(Mid([proj_name],1,4)='9995','Perspective',
IIf(Mid([proj_name],1,4)='9016','MONITOR Internal',
IIf(Mid([proj_name],1,4)='9011','MONITOR Internal',
IIf(Mid([proj_name],1,4)='9010','MONITOR Internal',
IIf(Mid([proj_name],1,4)>'99',IIf(Mid([proj_name],1,3)='999','MONITOR Internal','Admin'),'Client')
)
)
)
)
)
)
)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
from................
Your input will be appreciated
Thank you
