Not selecting if the varchar contains letters 1

[lhg1]

Hi

I'm usint this sql to sort a varchar field, and i works.

select field1, field2
from table
order by CAST(field1 AS INT)

but when just one row contains a letter the sql doesn't return any rows, until the data in the field is an integer again.

Is there a way to get it out anyway? - either excluding the one with letter or returning it.

Thanks
LHG

 

[markros]

Look for ISNUMERIC() function in BOL to exclude records or adjust the CASE statement.

 

[gmmastros]

Try this:

select field1, field2
from table
order by Case When IsNumeric(Field1 + '.0e0') 
              Then CAST(field1 AS INT)
              Else 0 
              End

Rows where field1 cannot be converted to int will be sorted as though the value is 0.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lhg1]

Interesting, but I can't get it to work

Incorrect syntax near the keyword 'then'.

I'm using it throu PHP and a function callede @mssql_query

Any Ideers?

 

[gmmastros]

Sorry. I missed a little part.

select field1, field2
from table
order by Case When IsNumeric(Field1 + '.0e0') [!]= 1[/!]
              Then CAST(field1 AS INT)
              Else 0
              End

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lhg1]

Great that works.

I was trying to empty fields to come last, maby you can spot how that is done

This was my attemt

when IsNumeric(Fixed_in__Vendor_ + '.0e0') = 1
 then CAST(Fixed_in__Vendor_ AS INT) 
when Fixed_in__Vendor_ = ""
 then 999999
else 99999 
End";

 

[bborissov]

Do not use double quotes in SQL Server queries. Use single quotes only.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

With this method, empty strings will return true from the IsNumeric function. So you will need to check that condition first.

when Fixed_in__Vendor_ = ""
 then 999999
when IsNumeric(Fixed_in__Vendor_ + '.0e0') = 1
 then CAST(Fixed_in__Vendor_ AS INT)
else 99999
End";

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lhg1]

Fantastic

Ended up with this.

when Fixed_in__Vendor_ = ''
 then 999999
when Fixed_in__Vendor_ = NULL
 then 999999
when IsNumeric(Fixed_in__Vendor_ + '.0e0') = 1
 then CAST(Fixed_in__Vendor_ AS INT)
else 99999
End";

Thanks

 

[bborissov]

You could try:

when ISNULL(Fixed_in__Vendor_, '') = ''
     then 999999
when IsNumeric(Fixed_in__Vendor_ + '.0e0') = 1
     then CAST(Fixed_in__Vendor_ AS INT)
else 99999
End";

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

You can't do this:

[tt][blue]when Fixed_in__Vendor_ = NULL[/blue][/tt]

Well, you can, but it won't do what you expect it to. You cannot use the = (equal) operator to compare nulls. If you want to compare nulls, you need to use the IS comparison.

[tt][blue]when Fixed_in__Vendor_ [!]is[/!] NULL[/blue][/tt]

For a quick example, paste this in to a query window and run it:

Select 'Null = Null' Where NULL = NULL
Select 'Null <> Null' Where NULL <> NULL
Select 'Null Is Null' Where NULL Is NULL
Select 'Not Null Is Null' Where Not NULL Is NULL

The only one that returns anything is "Null Is Null"

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom