SQL Server regex and if else in where clause

Question

This is my code:

$db = new COM("ADODB.Connection");
$dsn = "DRIVER={SQL Server}; SERVER={$server}; UID={$usr}; PWD={$pwd}; DATABASE={$dbname}";
$db->Open($dsn);
$sql = "SELECT o.CardCode, o.CardName, o.VatIDNum, o.AddID, o.Cellular, o.E_Mail, c.Address
            FROM ocrd o INNER JOIN crd1 c ON o.CardCode = c.CardCode
            WHERE o.Cellular = '$phone1' o.CardName LIKE N'%$lname%'";
$result = $db->execute($sql);

In the databese the o.Cellular column includes phone numbers that could be formatted with dashes/spaces/+ sign/braces so when I am checking WHERE o.Cellular = '$phone1' I need to reformat o.Cellular to only digits (the $phone1 already only digits).

The second problem is that if o.Cellular not equals $phone1, I want to check o.CardName LIKE N'%$lname%'.

So the current part of code doesn't works as I need.

Any help please...

Answer 1

REGEX is extremely limited in SQL Server. Here's an example to replace the following characters in your phone number: + ( ) - \s

SELECT 
    o.CardCode, 
    o.CardName, 
    o.VatIDNum, 
    o.AddID, 
    o.Cellular, 
    o.E_Mail, 
    c.Address
FROM 
    ocrd o 
    INNER JOIN 
        crd1 c ON 
        o.CardCode = c.CardCode
WHERE 
    replace(replace(replace(replace(replace(o.Cellular,'-',''),' ',''),'(',''),')',''),'+','') = '$phone1' 
    or o.CardName LIKE N'%$lname%'

--test example you can run to see the results of the code

declare @Cellular varchar(16) = '+1 (156) 555-7899'
select replace(replace(replace(replace(replace(@Cellular,'-',''),' ',''),'(',''),')',''),'+','')

--returns 1156555789