SQL Server 2008 r2 - T-SQL LIKE or PATINDEX to match characters OTHER than A-Z, 0-9, hyphen, period, underscore and tilde

Question

Please help me write a PATINDEX or LIKE statement to match characters other than:

• A-Z, 0-9, hyphen (-), period (.), underscore (_), and tilde (~)

I plan to use this in a scalar UDF with an nvarchar(200) input, which processes the input by:

1. Replace the non-matching characters with hyphen (-)
2. Replace two occurences of hyphen (--) with single (-)
3. Removes leading and trailing hypens (-)
4. Returns the processed input

This will be used to create part of an SEO-friendly URL e.g. /my-seo-friendly-url-1. I am very confident in doing this UDF, apart from the pattern-matching part. Regex-like stuff confuses me! Please help.

Thanks for your help in advance.

Answer 1

Probably best done in your application but

SELECT PATINDEX('%[^-a-zA-Z0-9.~_]%', @YourString COLLATE Latin1_General_BIN)

should do it in TSQL