dynamic casting of field type with case when

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 2005 Forums

Transact-SQL (2005)

dynamic casting of field type with case when

Author

Topic

aykutucar
Starting Member

5 Posts

Posted - 2009-02-02 : 14:49:59

hi,
I have a varchar column and I want to change my comparison
type according to the type of data inside that column.
This thing gives a type convert error.
Is there a way to make it work?
Thanks.

SELECT * FROM MyTable WHERE
use_cpc=CASE ISNUMERIC(use_cpc) WHEN 1 THEN 1 ELSE 'yes' END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-02-02 : 15:18:25

Why not:


SELECT * FROM MyTable WHERE 
ltrim(rtrim(use_cpc)) = '1' OR ltrim(rtrim(use_cpc)) = 'yes'

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-02 : 15:19:48

what are you trying to do ? Can you describe exactly what output you need ?

aykutucar
Starting Member

5 Posts

Posted - 2009-02-02 : 15:30:01

quote:
Originally posted by sakets_2000

what are you trying to do ? Can you describe exactly what output you need ?

What I am trying to do is I have a table with varchar fields but there may be numeric values in those fields. And there is no way for me to know which field would be which type so all the columns must be varchar. And I need to let user search on columns.

I think I cannot use something like
ltrim(rtrim(use_cpc)) = '1' OR ltrim(rtrim(use_cpc)) = 'yes'

because I also let user select the operator like =, <, >, <>, IN

So if there is a numeric value in a column I need to do something like column>5, column IN(2,5,8) etc.

if not then column='yes', column IN ('yes','maybe') etc.

thanks for your comments

If

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 09:35:43

quote:
Originally posted by aykutucar

hi,
I have a varchar column and I want to change my comparison
type according to the type of data inside that column.
This thing gives a type convert error.
Is there a way to make it work?
Thanks.

SELECT * FROM MyTable WHERE
use_cpc=CASE ISNUMERIC(use_cpc) WHEN 1 THEN 1 ELSE 'yes' END

ISNUMERIC is not fully reliable. you may use below instead

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

aykutucar
Starting Member

5 Posts

Posted - 2009-02-03 : 12:20:09

quote:
Originally posted by visakh16

quote:
Originally posted by aykutucar

hi,
I have a varchar column and I want to change my comparison
type according to the type of data inside that column.
This thing gives a type convert error.
Is there a way to make it work?
Thanks.

SELECT * FROM MyTable WHERE
use_cpc=CASE ISNUMERIC(use_cpc) WHEN 1 THEN 1 ELSE 'yes' END

ISNUMERIC is not fully reliable. you may use below instead

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

Thanks for the link.
Any ideas on the problem?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 12:31:07

will the search values be passed as a parametyer? if yes, whats parameter datatype?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources