2

How to retrieve rows which contain exactly 4 numbers in Microsoft SQL Server?

This query returns string more than 4 numbers

SELECT * 
FROM table 
WHERE Name LIKE '%[0-9][0-9][0-9][0-9]%';
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Please Provide us with sample data, and desired results. – ahmed abdelqader Mar 25 '18 at 11:02
  • When I execute query above, it show me result like- 'Microsoft Windows 2000 Professional in 24 hours'.(this string contains 6 numbers) and 'Secrets of Windows 2000 Server'(this string contains only 4 numbers). I need that my result table contains only strings with 4 numbers, not 6. How can do it? –  Mar 25 '18 at 11:09

2 Answers2

0

SQL Server is purposely bad at string manipulation, probably to promote Microsoft's client side tooling, like C# or Reporting Server. See for example this question from 2009. So the sane solution involves creating a CLR UDF.

But you can still do it in T-SQL with a lot of trickery. For example, you could create a user-defined function that removes all numbers from a string:

create function dbo.StripNumbers(@str nvarchar(max))
returns nvarchar(max)
as begin
  declare @i int = patindex('%[0-9]%', @str)
  while @i > 0
    begin
    set @str = stuff(@str, @i, 1, '')
    set @i = patindex('%[0-9]%', @str)
    end
  return @str
end

Then you can filter for strings that are 4 characters shorter when stripped:

select  *
from    YourTable
where   len(dbo.StripNumbers(txt)) = len(txt) - 4

Working example at SQL Fiddle.

Andomar
  • 232,371
  • 49
  • 380
  • 404
0

You could use wildcards for this:

select  *
from    Table
where   Name like '%[0-9]%[0-9]%[0-9]%[0-9]%'
        and Name not like '%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%'
MJH
  • 1,710
  • 1
  • 9
  • 19