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: - Replace the non-matching characters with hyphen (-) - Replace two occurences of hyphen (--) with single (-) - Removes leading and trailing hypens (-) - 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.

Accepted Answer

Probably best done in your application but

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

should do it in TSQL

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

Solution

Problem