Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Case... WHEN

Author  Topic 

Phailak
Starting Member

5 Posts

Posted - 2014-05-08 : 17:23:56
Hi,

I'm a programmer but having some issues with a case statement I want to use, might be in the format or maybe I am not understanding how to use it properly, not a lot of experience. The query was working fine until I had to change it to include a second condition (that you find confnum or conference in the string, it should extract the information to the same field confnum. Initially I was just checking against Confnum but realzied there is also some instances of conference):

CASE
WHEN charindex(''confnum='', Result) > 0 THEN
CASE
WHEN charindex('';'', Result, charindex(''confnum='', Result)) > 0 THEN
substring
(Result,
charindex(''confnum='', Result) + 8,
charindex('';'', Result, charindex(''confnum='', Result)) - charindex(''confnum='', Result) - 8
)
ELSE
substring
(Result,
charindex(''confnum='', Result) + 8,
len(Result) - charindex(''confnum='', Result) + 8
)
END
WHEN charindex(''conference: '', Result) > 0 THEN
CASE
WHEN charindex('';'', Result, charindex(''conference: '', Result)) > 0 THEN
substring
(Result,
charindex(''conference: '', Result) + 12,
charindex('';'', Result, charindex(''conference: '', Result)) - charindex(''conference: '', Result) - 12
)
ELSE
substring
(Result,
charindex(''conference: '', Result) + 12,
len(Result) - charindex(''conference: '', Result) + 12
)
END
ELSE NULL
END as confnum,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-08 : 17:31:23
What error are you getting? Why are there double quotes instead of single?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Phailak
Starting Member

5 Posts

Posted - 2014-05-08 : 17:33:42
156:Incorrect syntax near the keyword 'CASE'.

Double quotes because it is being assigned to a varchar variable
Go to Top of Page

Phailak
Starting Member

5 Posts

Posted - 2014-05-08 : 17:35:01
This was working:

CASE
WHEN charindex(''confnum='', Result) > 0 THEN
CASE
WHEN charindex('';'', Result, charindex(''confnum='', Result)) > 0 THEN
substring
(Result,
charindex(''confnum='', Result) + 8,
charindex('';'', Result, charindex(''confnum='', Result)) - charindex(''confnum='', Result) - 8
)
ELSE
substring
(Result,
charindex(''confnum='', Result) + 8,
len(Result) - charindex(''confnum='', Result) + 8
)
END
ELSE NULL
END as confnum,

EDIT: Oops sorry for double post!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-08 : 17:43:53
I think the issue is elsewhere as that syntax checks out for me after I make some changes since you didn't provide the whole thing. Simple changes like: switched double quotes to single, added select, added column after "as confnum," and added from table.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Phailak
Starting Member

5 Posts

Posted - 2014-05-08 : 17:45:06
Hmmm ok thanks, I'll play some more, my worry was more with the format so if it seems good, I'll try to isolate the rest!
Go to Top of Page

Phailak
Starting Member

5 Posts

Posted - 2014-05-08 : 17:50:07
Wow, seems like the variable holding the SQL statement was being truncated even though I had varchar(max) which I thought would hold a LOT of characters, oh well lesson learn thanks for the help
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-08 : 17:55:32
quote:
Originally posted by Phailak

Wow, seems like the variable holding the SQL statement was being truncated even though I had varchar(max) which I thought would hold a LOT of characters, oh well lesson learn thanks for the help



I don't think that's the issue as varchar(max) can hold 2 billion characters. I seriously doubt you've hit that limit.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -