Equivalent regex in T-SQL with start/end input markers

Question

I'm trying to get the following RegEx to work:

^[a-zA-Z][a-zA-Z ''.-]+[a-zA-Z]$

It should allow any alphas, space, apostrophe, full stop and hyphen as long as the beginning and last chars as alphas.

John        - ok
John Smith  - ok
John-Smith  - ok
John.Smith  - ok
.John Smith - not ok
John Smith. - not ok

When I use this in T-SQL it doesn't seem to work and I'm not sure if it the input start/end markers that are not compatible in T-SQL. How do I translate this to valid T-SQL?:

CREATE Function [dbo].[IsValidName](@value VarChar(MAX))
RETURNS INT
AS
Begin
    DECLARE @temp INT

    SET @temp = (
        SELECT                  
            CASE WHEN @value LIKE '%^[a-zA-Z][a-zA-Z ''.-]+[a-zA-Z]$%' THEN 1           
            ELSE 0
        END     
    )


    RETURN @Temp
End

The LIKE operator does not use regular expressions. It uses a much more limited set of wildcards. Among other things, there is no equivalent for `^`, `$`, or `+`. Take a look at the doc [here](https://msdn.microsoft.com/en-us/library/ms179859.aspx) or [here](https://technet.microsoft.com/en-us/library/ms187489(v=sql.105).aspx). If you want full regex support, you'll probably have to use CLR functions or procedures. — Bacon Bits, Mar 02 '16 at 16:00

score 2 · Answer 1 · edited May 23 '17 at 12:15

2

T-SQL doesn't support regular expressions "out of the box". Depending on what environment you are using, there are different solutions, but none will probably be "pure T-SQL". In a Microsoft environment you can use CLR procedures to achieve this.

See SQL Server Regular expressions in T-SQL for some options.

edited May 23 '17 at 12:15

Community

1
1

answered Mar 02 '16 at 15:59

TheQ

6,858
4
35
55

score 0 · Accepted Answer · answered Mar 02 '16 at 16:40

I made SOMETHING like this to scrub data, to remove non-alpha characters, I've slightly modified it to fit your needs

CREATE Function [dbo].Func (@Temp VarChar(1000))
Returns VarChar(1000)
AS
BEGIN
DECLARE @Len INT = LEN(@Temp)
DECLARE @RETURN INT
Declare @KeepValues as varchar(50)
Set @KeepValues = '%[^a-z^ ]%'
IF PatIndex(@KeepValues, @Temp) = 1
BEGIN
    Set @RETURN = 0
END
IF PATINDEX(@KeepValues, @Temp) = @Len
BEGIN
    SET @RETURN = 0
END
IF PATINDEX(@KeepValues, @Temp) = 0
SET @RETURN = 1
IF @RETURN IS NULL
 BEGIN
   SET @Return = 1
 END
 RETURN @RETURN
END

This is assuming you would not need to do any sort of data scrubbing for restricted characters. If you need to scrub for restricted characters let me know we can add a little more in there but based on your dataset this will return the correct answers

Equivalent regex in T-SQL with start/end input markers

2 Answers2