SQL pattern matching using regular expression

Question

Can we use Regex i.e, Regular Expression in SQL Server? I'm using SQL-2012 and 2014 and there is an requirement to match and return input from my stored procedure.

I can't use LIKE in this situation since like only returns matching words, Using Regex I can match whole bunch of characters like Space, Hyphen, Numbers.

Here is my SP

--Suppose XYZ P is my Search Condition
Declare @Condition varchar(50) = 'XYZ P'

CREATE PROCEDURE [dbo].[usp_MATCHNAME]
    @Condition varchar(25)
as
Begin

select * from tblPerson
where UPPER(Name) like UPPER(@Condition) + '%'
-- It should return both XYZ P and xyzp
End

Here my SP is going to return all matching condition where Name=XYZ P, but how to retrieve other Column having Name as [XYZP, XYZ-P]

and if search condition have any Alphanumeric value like

--Suppose XYZ 1 is my Search Condition
Declare @Condition varchar(50) = 'XYZ 1'

Then my search result should also return nonspace value like [XYZ1, xyz1, Xyz -1].

I don't want to use Substring by finding space and splitting them based on space and then matching.

Note: My input condition i.e., @Condition can have both Space or Space less, Hyphen(-) value when executing Stored Procedure.

Arulkumar · Accepted Answer · 2016-07-16 06:47:13Z

1

Use REPLACE command.

It will replace the single space into %, so it will return your expected results:

SELECT * 
FROM tblPerson
WHERE UPPER(Name) LIKE REPLACE(UPPER(@Condition), ' ', '%') + '%'

answered Jul 16, 2016 at 6:47

Arulkumar

13.3k14 gold badges51 silver badges76 bronze badges

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

3 Comments

Amar Over a year ago

That works... But there is no guarantee that everytime SP will receieve parameter having space between them. We can even have hyphen

Arulkumar Over a year ago

@Amar: Replace will work only if the parameter having space.

Amar Over a year ago

Thanks for showing replace but this won't work if i have to find its equivalent character like if @Condition = 'XYZ 1'

Collectives™ on Stack Overflow

SQL pattern matching using regular expression

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related