CODEWITHSUNDEEP
sqlsql-servert-sql
Skorpion
Skorpion

Reputation: 35

Text and Numbers Where Condition SQL Server 2016

I have one table (Number) where in one column have text and numbers feed.

and table have records like this 

 NULL
 IGNORE
 domestic
 dead
 domestic 
 aggregated with 002
 shipperless
 Joint AQ
 tp
 IGNORE 
 1
 14
 0
 127207 AGG
 6
 20.56
 11.641
 537, 506
 21
 1,64,1395
 41727 kWh
 23
 25.9.2016 
 6
 36254
 36285
 36304
 36295
 36253

and my query is like this

select * from table
where number >= 10000 and number <100000

when i write the query its give me error of 

Conversion failed when converting the nvarchar value 'TPD' to data type int.

please any help me if any one understand the question

Upvotes: 0

Views: 50

Answers (3)

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

You can use TRY_PARSE like this :

select * from table
where TRY_PARSE(number AS numeric(10,2)) between 10000 and 100000

Upvotes: 0

Mato
Mato

Reputation: 29

I can't post a comment, but if you only want to convert integers, try:

SELECT * FROM [TABLE]
WHERE TRY_CAST([number] AS INT) BETWEEN 10000 AND 100000

Upvotes: -1

Thom A
Thom A

Reputation: 95589

Use TRY_CONVERT:

Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

SELECT *
FROM [TABLE]
WHERE TRY_CONVERT(decimal(10,2),[number]) >= 10000
  AND TRY_CONVERT(decimal(10,2),[number]) < 100000;

Upvotes: 3

Related Questions