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.

Answer 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), ' ', '%') + '%'