case statement, charindex and substring problem....

Question

Hello everyone,

I've create a stored procedure with a Case statement and the case statement with charindex and substring... all values are return correctly but only the Case statement is incorrect, here's the case statement bit :

....

(CASE CHARINDEX(SUBSTRING (Job.ClaimDetails, 1, 50),'.') WHEN 0
                THEN SUBSTRING (Job.ClaimDetails,1, CHARINDEX('.', Job.ClaimDetails)-1)
                ELSE SUBSTRING (Job.ClaimDetails, 1, 50)+ '...'
                END) AS ClaimDetails,

.....

What I want the Case statement to do is : When the job.claimdetails's substring from 1 to 50 which contain a dot, then I want the return value from the first character to the dot. Otherwise, I want the return value from the first character to the 50th characters.

However, I excuted it, I found out its only this line is working : SUBSTRING (Job.ClaimDetails,1, CHARINDEX('.', Job.ClaimDetails)-1)

I got the return value no matter where the dot is...

does anyone could give me some advise please?

Many thanks in advance

---

ninjaPerson

Answer 1

In the first CHARINDEX, it looks like the parameters are in the incorrect order, so you are searching the string '.' for the long text, as opposed to the other way around.

CASE CHARINDEX(SUBSTRING (Job.ClaimDetails, 1, 50),'.') WHEN 0

Should be:

CASE CHARINDEX('.',SUBSTRING (Job.ClaimDetails, 1, 50)) WHEN 0

Thanks,
Sam Lester (MSFT)

---

My Blog

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 2

Hello Samuel Lester,

Thanks for your reply, however, I try using your code instead of mine but still not work....

....

(CASE CHARINDEX('.',SUBSTRING(Job.ClaimDetails, 1, 50)) WHEN 0
                THEN SUBSTRING (Job.ClaimDetails,1, CHARINDEX('.', Job.ClaimDetails)-1)
                ELSE SUBSTRING (Job.ClaimDetails, 1, 50)+ '...'
                END) AS ClaimDetails,

.....

Even the dot is located after 100s characters, I still got the return value of 100s characters Not the first 50 characters....

---

ninjaPerson

Answer 3

Try

....

(CASE when CHARINDEX('.',SUBSTRING (Job.ClaimDetails, 1, 50))=0
                THEN SUBSTRING (Job.ClaimDetails,1, CHARINDEX('.', Job.ClaimDetails)-1)
                ELSE SUBSTRING (Job.ClaimDetails, 1, 50)+ '...'
                END) AS ClaimDetails,

.....

Run it, which should be fine.

---

Many Thanks & Best Regards, Hua Min

Answer 4

How about this :

select case when CHARINDEX('.',Job.ClaimDetails)>0 then SUBSTRING(Job.ClaimDetails,1,CHARINDEX('.',@v)-1) else SUBSTRING(Job.ClaimDetails,1,50) end 

FROM yrtable

---

Thanks and regards, Rishabh K

Answer 5

(CASE CHARINDEX('.',SUBSTRING(Job.ClaimDetails, 1, 50)) WHEN 0
                

Doesn't this mean that the dot is not present in the string and in this case shouldn't the THEN function contain SUBSTRING (Job.ClaimDetails, 1, 50). I think you have misplaced the result....move the one from THen to Else and the one in Else to Then 

---

Murali Krishnan

Answer 6

Yes Murali,

You're right ! I have misplace the result ...

it works, Thank you so much :)

---

ninjaPerson