CODEWITHSUNDEEP
sqlsql-servert-sql
simpleSharp
simpleSharp

Reputation: 3

How to select a number thats in a string format

I Have a table that has a column that can have values like

Id     FileNumber
------------------
1      0025
2      CDE01
3      0035
4      25
5      45
6      250
7      XYZ1
8      2500

Now if I want to select the row that has the FileNumber 25, how can I get that. I do not want the row that has 250 or 2500. The search should only get 0025 and 25. This column is of type varchar. However, I should be able to search by any value like XYZ1 OR 0025 OR 250

I tried using the Like operator eg: WHERE FileNumber Like '%25%' also tried WHERE FileNumber Like '25%' does not give me the desired results Any help will be greatly appreciated

Upvotes: 0

Views: 216

Answers (5)

gbn
gbn

Reputation: 432200

You want anything that

  • matches exactly (25)
  • ends in with only leading zeroes) (0025)

This code does that with some LIKE matching

WITH sample AS (
  SELECT '0025' AS col
  UNION ALL SELECT 'CDE01'
  UNION ALL SELECT '0035'
  UNION ALL SELECT '25'
  UNION ALL SELECT '45'
  UNION ALL SELECT '250'
  UNION ALL SELECT 'XYZ1'
  UNION ALL SELECT 'XYZ125'
  UNION ALL SELECT '125'
  UNION ALL SELECT '2500')
SELECT
   s.col
FROM
    sample s
WHERE
  col = '25'
  OR
  (REPLACE(col, '25', '00') NOT LIKE '%[^0]%' AND col LIKE '%25')

Upvotes: 1

Jody
Jody

Reputation: 8291

If it's safe to fix the data...do that.

Try something like this

update set filename = cast(filename as int) where filename like '%[1-9]%' --has a number in it and filename like '%[^a-z]%' -- doesn't have a letter in it

Upvotes: 0

Bhavik Goyal
Bhavik Goyal

Reputation: 2796

Please use this to get all the rows from the table having the values that exactly matches the comma seprated values.

SELECT * FROM TABNAME WHERE FILENUMBER in ('25','0025','xyz')

It will select all the rows who contains the filenumber as 25 or 0025 or xyz.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332531

Based on the sample data, I'll assume the numbers you need to retrieve are integers. So you could cast them to integers if they are numeric using something like this:

CASE WHEN ISNUMERIC(s.col) = 1 THEN CAST(s.col AS INT) END

Example:

WITH sample AS (
  SELECT '0025' AS col
  UNION ALL
  SELECT 'CDE01'
  UNION ALL
  SELECT '0035'
  UNION ALL
  SELECT '25'
  UNION ALL
  SELECT '45'
  UNION ALL
  SELECT '250'
  UNION ALL
  SELECT 'XYZ1'
  UNION ALL
  SELECT '2500')
SELECT CASE WHEN ISNUMERIC(s.col) = 1 THEN CAST(s.col AS INT) END
  FROM sample s

You can use that in a derived table/inline view to compare against. It's possible you could add a computed column using the logic to the table.

Upvotes: 4

S M Kamran
S M Kamran

Reputation: 4503

How about this?

SELECT * FROM [TABLE NAME] WHERE FILENUMBER LIKE '%25'

Upvotes: 0

Related Questions