retrieve value based on subfunction

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

General SQL Server Forums

New to SQL Server Programming

retrieve value based on subfunction

Author

Topic

varalakshmi
Yak Posting Veteran

98 Posts

Posted - 2008-12-16 : 07:59:41

Hi,
I have to select a column value from a table based on a result of a string function.

For example,
If substr(column1,1,1) = 'A'
then the query should return column1 else should return column2.
Please help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 08:01:41

[code]select case when column1 like 'A%' then column1 else ciolumn2 end as yourcolumn from yourtable[/code]

varalakshmi
Yak Posting Veteran

98 Posts

Posted - 2008-12-17 : 00:02:10

I have to use string functions after 'when' which is not possible.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 00:07:36

can you elaborate what string function you want to use?

varalakshmi
Yak Posting Veteran

98 Posts

Posted - 2008-12-17 : 00:18:37

It should be of the below structure:

select case when substr(column1,1,2) ='AB' then column1
case when substr(column1,1,2) ='AC' then column2
else column3 end as yourcolumn from yourtable

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-17 : 00:34:33

quote:
Originally posted by varalakshmi

It should be of the below structure:

select case when substr(column1,1,2) ='AB' then column1 end
case when substr(column1,1,2) ='AC' then column2
else column3 end as yourcolumn from yourtable

u should not keep case here r u have to write another case when then end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 00:48:10

quote:
Originally posted by varalakshmi

It should be of the below structure:

select case when substr(column1,1,2) ='AB' then column1
~~case~~ when substr(column1,1,2) ='AC' then column2
else column3 end as yourcolumn from yourtable

no need of extra case
also make sure column1,column2 & column3 are of same datatype

varalakshmi
Yak Posting Veteran

98 Posts

Posted - 2008-12-17 : 01:10:51

Thanks visakh..
It worked...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 01:12:56

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources