CODEWITHSUNDEEP
sqlsql-serversql-server-2014
Mathan
Mathan

Reputation: 83

Multiple occurrences of a character match using regular expression in sql server 2014

In SQL Server 2014, i want to select a row which contains a word that is not present inside any of the angled brackets <>.

Sample Data:

 Row 1 --> <div class="highlight"><b>Maddy</b></div>
 Row 2 --> <div><b>This is highlighting an feature.</b></div>"

Here i want to filter only second row. So i used a query like

select * from table where column like '%<%>[a-zA-z0-9]*'+'highlight'+'%<%>%'"

Upvotes: 0

Views: 3132

Answers (3)

Shaneis
Shaneis

Reputation: 1085

You've nearly had the answer yourself Mathan.

The only problem that I can see with your code is that you were treating the LIKE expression as a dynamic expression.

If you are searching for a specific value in the middle of a substring then you need to wrap it in wildcards, even if you are joining search expressions together, e.g. LIKE '%highlight% + '%<%>%'

DECLARE @table table ( [column] varchar(100));

insert into @table ([column])
 SELECT * FROM 
 (
 VALUES
 ('<div class="highlight"><b>Maddy</b></div>'), 
 ('<div><b>This is highlighting an feature.</b></div>')
 ) as [table] ([column]);

 --SELECT * FROM @table;

select 
 [text_value] = PATINDEX('%<div>%', [column]) + LEN('<div>'),
  SUBSTRING(
  [column] -- what you're searching
  , PATINDEX('%<div>%', [column]) + LEN('<div>') -- after the '<div>', 
                                                 -- need to add the length of the 'div' as PATINDEX returns the starting location 
  , PATINDEX('%</div>%', [column]) - LEN('</div>')  -- until the '</div>'
  )
 , *
from @table 
    where [column] like 
        '%<%>[a-zA-z0-9]%'  -- you need to end these with the wildcard 
        +'%highlight%'      -- or SQL-Server thinks it's the end of the sentence
        +'%<%>%';

EDIT:

Adding in the PATINDEX can be used to remove the '<>' from the string. Example above removes the <div></div> but you can use that to remove any others as necessary e.g. <b></b>

Upvotes: 0

MarkAnderson2001
MarkAnderson2001

Reputation: 11

Attempt:

select * from table_name where column_name like 'T[a-z]%'

select * from table_name where column_name like '[T]%'

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

I believe this is what you need:

LIKE 'T%[a-z]%[a-z]%'

Now would be a good time to familiarize yourself with what you can and cannot do with the LIKE operator.

Upvotes: 0

Related Questions