Case... WHEN - SQL Server Forums

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/

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

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!

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/

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!

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

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources