Convert varchar to int in WHERE clause

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)

Convert varchar to int in WHERE clause

Author

Topic

fralo
Posting Yak Master

161 Posts

Posted - 2014-12-01 : 08:56:31

Hi,

I have a database field CALLNO of varchar datatype. The values are 100 to 500 with a few exceptions in the format of F10, F12, F25, etc. I want to do a check in the WHERE clause to get this entire range.

i.e. WHERE callno >= 100 and callno <= 500

But I don't know how to convert it to INT, and include as well the above exceptions in the result set.

Thanks for any help you could provide.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-01 : 09:06:05

Well, clearly values like Fnn are neither >= 100 or <= 500 (since they are not numbers, the comparison is invalid), so why not exclude them in the first place?


where 1 = 
  case
    when left(callno,1) = 'F' then 0
    when callno >= 100 and callno <= 500 then 1
    else 0
  end

Note that case will execute the clauses in order written (L-R, T-B) and so eliminate the alphanumeric values before testing for numerics.

Note also that this is not a bulletproof solution. e.g. if in the future new values appear like G10,G20, etc. this will fail.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2014-12-01 : 09:09:42

So why does F10 fall between 100 and 500, and not 100 and 700? Why does F10 not fall between 0 and 99?

EDIT: i see. you do NOT want the odd ones. Got it.
EDIT2 : oh, you DO want them.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

fralo
Posting Yak Master

161 Posts

Posted - 2014-12-01 : 09:14:21

Thanks gbritton. Yes, I want to include values 100 to 500 and any values LIKE 'F%'. One more thing. The values in the db are padded with two spaces as in ' 100', ' 150', ' F10'.

I tried this...

SELECT *
FROM [empmast].[dbo].[EMPMAST]
where 1 =
case
when left(ltrim(callno),1) = ' F' then 0
when callno >= 100 and callno <= 500 then 1
else 0
end

But I get an error saying "Conversion failed when converting the varchar value ' F12' to data type int."

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2014-12-01 : 09:26:57

Perhaps this?

SELECT *
FROM [empmast].[dbo].[EMPMAST]
where callno =
case
when left(ltrim(callno),1) like 'F' then callno
when left(ltrim(callno),3)= '500' or left(ltrim(callno),3) like '[1-4][0-9][0-9]' then callno
end

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-01 : 09:29:04

Think about what you have:

callno => ' F10'
ltrim(callno) => 'F10'
left(ltrim(callno),1) => 'F'
(left(ltrim(callno),1) = ' F') => false -- always false, since you're comparing 1 character to two and SQL pads with spaces for the comparison.

fralo
Posting Yak Master

161 Posts

Posted - 2014-12-01 : 09:31:29

Almost Don. But the results are including values greater than 500. Thanks for your help thus far.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-01 : 10:30:33

Here's a different approach, using CROSS APPLY to parse the column callno:


SELECT *
FROM [empmast].[dbo].[EMPMAST]
cross apply (
	select case
		when ltrim(callno) not like '%[^0-9]%'  then ltrim(callno)
		when left(ltrim(callno),1) like '[A-Z]' then SUBSTRING(ltrim(callno),2,len(callno))
	) c(callno)
where callno > = 100 and callno < = 500

Hopefully you have enough options to work with and finish you query!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources