search using regex functionality in SQL

Question

I am searching for a keyword using SQL server and Regex Functionality.

we have a data like leaky gas, leaked gas, and leaking gas.

SELECT [Text field]
FROM [Global database] 
text field is like '%leak[a-z]{1,3} gas%'

Could this help you ? Select [Text field] from [Global database] text field is like '%leak%gas%' — Pedro Martins
– Pedro Martins, Commented Mar 2, 2018 at 23:56
I don't want text search where like "the leak is not from gas." — Naveen Chebrolu
– Naveen Chebrolu, Commented Mar 3, 2018 at 0:04

Gordon Linoff · Accepted Answer · 2018-03-03 00:05:06Z

1

SQL Server does not support regular expressions (unless you add your own UDF). It does however extend the LIKE functionality for some basic wildcards.

You can do what you want as:

where field like '%leak[a-z] gas%' or
      field like '%leak[a-z][a-z] gas%' or
      field like '%leak[a-z][a-z][a-z] gas%'

answered Mar 3, 2018 at 0:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kalaolani · Accepted Answer · 2018-03-03 01:06:22Z

0

You can create a regex CLR that can be used in SQL.

https://www.red-gate.com/simple-talk/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/

answered Mar 3, 2018 at 1:06

kalaolani

3221 silver badge16 bronze badges

Collectives™ on Stack Overflow

search using regex functionality in SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related