Geek asks and answers

MS SQL template for

How can I do a sql search with these rules:

  • Field
  • must start from zero or one or more "0" Field
  • should end some number, for example 555

In other words, the template must match the following entries:

'555'
'0555'
'00555'
etc

Can sql do this in a single select query (without line breaks or something else)? Thank.

+3
source share
3 answers

Perhaps so:

WHERE Field LIKE '%555' AND Field NOT LIKE '%[^0]%555'

Try this live on SQL script .

+9
source

may be like that

SELECT * FROM table where CAST(field AS int) = CAST('your input' AS int)
+1
source

, ( " (" none ")) ​​@timus2001 answer:

WHERE
  CASE
    WHEN Field NOT LIKE '%[^0-9]%' AND LEN(Field) <= 38
    THEN CAST(CAST(Field AS decimal(38, 0)) AS varchar(38))
    ELSE Field
  END = '555'

A length test is necessary if the value can be too large to convert to the longest 1 possible numeric type. You can simply use the condition LEN(Field) = 3instead LEN(Field) <= 38, but then you have to remember to change this part if the requirement '555'has changed to something else. Be that as it may, you will need to replace the string '555'if necessary (and remember that it should never exceed 38 characters).

1 in terms of decimal digits or scales.

0
source

More articles:


All Articles