• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 576
  • Last Modified:

Varchar to Numeric Search

I have a table that contains a InvoiceField varchar field
some of these record field has a value of 0000123456789  and some have a value of 00123456789
I need to be able to returen all records where varchar field is 0000123456789 , 00123456789 or 123456789

EXAMPLE:
SELECT * FROM MyTable
WHERE
DO YOUR STUFF  = 123456789

0
Phil Chapman
Asked:
Phil Chapman
  • 3
  • 2
  • 2
  • +2
4 Solutions
 
Einstine98Commented:
SELECT * FROM TABLE
WHERE convert (decimal, Myfield) = 123456789
0
 
Phil ChapmanAuthor Commented:
When I try the code above I get a Error converting data type varchar to numeric  
0
 
Phil ChapmanAuthor Commented:
I check the table and it had a record where the InvoiceField was blank which caused the error.  Can the above query be midified not to return a error if the field should have non numeric data.  But instead just to ignore the record.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Anthony PerkinsCommented:
SELECT * FROM MyTable
WHERE RIGHT(YourColumn, 9) = '123456789'
0
 
jrb1Commented:
Are the values always numeric?  If so, maybe:

SELECT * FROM MyTable
WHERE cast(yourfield as float)  = 123456789

or to trap for conversion errors  (if this works....it's OK, but not the best of SQL Server's functions)

SELECT * FROM MyTable
WHERE isnumeric(yourfield)
and cast(yourfield as float)  = 123456789
0
 
Anthony PerkinsCommented:
You can also take advantage of the fact that T-SQL uses short-circuit logic and use something like this:
SELECT * FROM MyTable
Where ISNUMERIC(Col1) = 1
           And CAST(Col1 as bigint) = 123456789
0
 
Scott PletcherSenior DBACommented:
>> SELECT * FROM MyTable
WHERE RIGHT(YourColumn, 9) = '123456789' <<

Technically | theoretically this might return incorrect results because of values such as '3123456789', '51123456789', etc..

0
 
Phil ChapmanAuthor Commented:
SELECT * FROM MyTable
Where ISNUMERIC(Col1) = 1
           And CAST(Col1 as bigint) = 123456789

or

SELECT * FROM MyTable
Where ISNUMERIC(Myfield) = 1
AND convert (decimal, Myfield) = 123456789


Seems to work fine.  This table has several million records the  InvoiceField  is a IX_Index Will the  ISNUMERIC and CAST or CONVERT have verry much effect on the speed of the search.
0
 
jrb1Commented:
Yes it will.  You could create a computed column:

ALTER TABLE YourTable
ADD YourComputedColumn AS CAST(Col1 as bigint)

Then index it:

CREATE INDEX ComputedColumnIndex
ON YourTable(YourComputedColumn)
0
 
Scott PletcherSenior DBACommented:
Yes, a *huge* impact -- SQL will simply *not* use an index for the lookup.

Honestly, you'd be better off using a method similar to the one below, as dopey as it looks, assuming there aren't leading spaces in the InvoiceField that would throw off a comparison, since SQL should then be able to use the index:

SELECT ...
FROM MyTable
INNER JOIN (
    SELECT '0000000000123456789' AS inv
    UNION ALL
    SELECT '000000000123456789'
    UNION ALL
    SELECT '00000000123456789'
    UNION ALL
    SELECT '0000000123456789'
    UNION ALL
    SELECT '000000123456789'
    UNION ALL
    SELECT '00000123456789'
    UNION ALL
    SELECT '0000123456789'
    UNION ALL
    SELECT '000123456789'
    UNION ALL
    SELECT '00123456789'
    UNION ALL
    SELECT '0123456789'
    UNION ALL
    SELECT '123456789'
) AS searchFor ON searchFor.inv = MyTable.invoiceField
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now