Menu
I have a varchar field in a SQL Server database that stores phone numbers in many different ways, but they are all phone number essentially.
Examples:
8181234564
(818) 123 4564
818 - 123 - 4567
I was hoping I can use regex to strip out all non-numeric characters and then perform a like or "=" on .. can I do that?
forgot to mention: I only have read access.
706
You can use RegEx in many languages like PHP, Python, and also SQL. RegEx lets you match patterns by character class (like all letters, or just vowels, or all digits), between alternatives, and other really flexible options.
You can specify precise and complex expressions using regex to find and replace various strings and patterns. In tools, such as SSMS, you can specify the regex patterns in the Find What and Find and Replace options.
Unlike MySQL and Oracle, SQL Server databases don't support built-in RegEx functions. However, SQL Server offers the following built-in functions to tackle such complex issues: LIKE. PATINDEX.
If you only have read access you probably cant create functions either.
If you can create a function you could use some of the existing solutions. If not, this is ugly, but it'd work for your examples:
declare @string varchar(50)
set @string = '(818) 123 - 4564'
select replace(replace(replace(replace(@string,'(',''),' ',''),')',''),'-','')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
