Regular expressions inside SQL Server

sql sql-server regex expression
Jack picture Jack · Dec 26, 2009 · Viewed 131.4k times · Source

I have stored values in my database that look like 5XXXXXX, where X can be any digit. In other words, I need to match incoming SQL query strings like 5349878.

Does anyone have an idea how to do it?

I have different cases like XXXX7XX for example, so it has to be generic. I don't care about representing the pattern in a different way inside the SQL Server.

I'm working with c# in .NET.

Answer

dan picture dan · Dec 26, 2009

You can write queries like this in SQL Server:

--each [0-9] matches a single digit, this would match 5xx
SELECT * FROM YourTable WHERE SomeField LIKE '5[0-9][0-9]'

Related questions

Perform regex (replace) in an SQL query

What is the best way to replace all '&lt' with < in a given database column? Basically perform s/&lt[^;]/</gi Notes: must work in MS SQL Server 2000 Must be repeatable (and not end …

Read More
How to use regular expression in TSQL?

I'd need to have a regular expression so that I can find all invalid email addresses in our database table. SELECT * FROM dbo.[Users] AS u WHERE u.EmailAddress not like 'regular expression of valid email addresses' Does SQL Server …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More