November 29, 2012 at 11:56 am
SELECT UniqueIDSystem
,FullLastFirstName
,Position PositionOrig
,case Position
when (CHARINDEX('M',Position)!=0 And CHARINDEX('D',Position)!=0 ) then 'MD'
end PositionCorrected
FROM tblPhysicianList
I actually expected this code to work and was surprised to get an error
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '!'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'PositionCorrected'.
So ... we can not use Charindex inside Case?
Thanks
November 29, 2012 at 12:18 pm
(CASE WHEN (CHARINDEX('M',Position)!=0 And CHARINDEX('D',Position)!=0) THEN 'MD'
ELSE POSITION -- ??
END) PositionCorrected
OR
(CASE WHEN (CHARINDEX('M',Position)>0 And CHARINDEX('D',Position)>0) THEN 'MD'
ELSE POSITION -- ???
END) PositionCorrected
November 29, 2012 at 12:20 pm
valeryk2000 (11/29/2012)
SELECT UniqueIDSystem
,FullLastFirstName
,Position PositionOrig
,case Position
when (CHARINDEX('M',Position)!=0 And CHARINDEX('D',Position)!=0 ) then 'MD'
end PositionCorrected
FROM tblPhysicianList
I actually expected this code to work and was surprised to get an error
Msg 102, Level 15, State 1, Line 6Incorrect syntax near '!'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'PositionCorrected'.
So ... we can not use Charindex inside Case?
Thanks
You can do that but the way you are using CASE is not going to work. You said Case Position when.
That means you are trying to evaluate the value of Position.
The following will work just fine.
;with cte as
(
select 'SomeText column and something' as Position
)
select Case when (CHARINDEX('M',Position)!=0 And CHARINDEX('D',Position)!=0 ) then 'MD' end PositionCorrected
from cte
You may want to consider adding an else, otherwise the value will be NULL.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 29, 2012 at 12:22 pm
Thanks. Same error msg
November 29, 2012 at 12:24 pm
Sean, I'm silly, you are right. Sorry, it was a long day ...
November 29, 2012 at 12:26 pm
valeryk2000 (11/29/2012)
Sean, I'm silly, you are right. Sorry, it was a long day ...
No problem. Sometimes it just takes a new set of eyes looking at it. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 29, 2012 at 1:49 pm
... especially it this set belongs to a guy like you 🙂
November 29, 2012 at 1:56 pm
valeryk2000 (11/29/2012)
... especially it this set belongs to a guy like you 🙂
:blush: Gosh thanks for the compliment. Sadly as I am getting older my eyes are getting worse and worse. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply