Search by numeric Value

Using Sql Server 2005

Table1

ID Value

ABC001
BCE002
.开发者_StackOverflow..

I have search column, when i search the id like this "001" it is showing empty, when I search "ABC001" then it is showing the values.

Query

Select * from table where id = '" & txtid & "'

txtid = textbox

I want to search only the numeric value in the ID.

How to write a query for the numeric search.

Need Query Help

select * from MyTable
where Value LIKE '[A-Z][A-Z][A-Z]001'

OR

select * from MyTable
where Value LIKE '[A-Z][A-Z][A-Z]' + RIGHT('000' + CONVERT(VARCHAR, @id), 3) 

SELECT * FROM Table1 WHERE id LIKE '%001'

The answer given by Mitch Wheat will work if every ID you give is 3 letters in caps followed by a numberical value which has 3 digits.

The answer given by rayman86 essentially disregards anything in the front. It merely looks for anything that ends with 001.

If the length of the ID varies and/or the first few characters are not even letters, it's better to use the code that rayman86 has provided. So just use the SELECT * FROM Table WHERE ID LIKE '%Numbers' where Numbers is your numerical value.

A literal interpretation of the question without making any assumptions:

Create this function

create function dbo.numericonly(@s varchar(max))
returns float as
begin
declare @i int set @i = 1
while @i <= len(@s)
  if ascii(substring(@s,@i,1)) between 48 and 57
    set @i = @i + 1
  else
    set @s = stuff(@s,@i,1,'')
return @s
end
GO

Use this query

Select * from table1 where dbo.numericonly(id) = '" & txtid & "'