Performing string functions on columns in a SQL Server statement using C#

Question

I have a SQL statement which - trimmed down for this question - looks like this:

SELECT * 
FROM Customers
LEFT JOIN SMS_Blacklist ON Customers.MobileNumber = SMS_Blacklist.Mobile_Number
WHERE SMS_Blacklist.Mobile_Number IS NULL

This works for the most part however the database has no data continuity or consistency and when the data looks like this:

Customer.MobileNumber = "07123 456789",
SMS_Blacklist.Mobile_Number = "07123456789"

it returns the record when it shouldn't (because the phone numbers don't match).

My question is, is it possible to perform a string function (i.e. string.replace(" ", "")) in the middle of the SQL statement so it might look something like this (I think probably not):

...
ON (Customers.MobileNumber).replace(" ","") = SMS_Blacklist.Mobile_Number
...

or how could I achieve something like this?

I am creating the SQL statement using new SqlCommand() in C#.

Gordon Linoff · Accepted Answer · 2017-03-16 11:31:08Z

3

Rectifying phone numbers is very tricky. But if you just want to remove spaces and hyphens you can do replace() in the SQL:

SELECT c.
FROM Customers c LEFT JOIN
     SMS_Blacklist b
     ON b.Mobile_Number = REPLACE(REPLACE(c.MobileNumber, ' ', ''), '-', '')
WHERE b.Mobile_Number IS NULL;

answered Mar 16, 2017 at 11:31

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Performing string functions on columns in a SQL Server statement using C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related