SQL Server Express LocalDB: update a value if it begins with a certain digit

Question

We are currently storing a load of customer mobile numbers, however most of them are missing the leading 0.

How can I add a 0 in SQL to the front of all mobile numbers where the first digit equals 7?

@MarkRotteveel From poking around it appears that localdb runs on SQL Server. — Tim Biegeleisen, May 18 '22 at 15:58
@TimBiegeleisen When I posted the comment there was no mention of localdb (though even then, the specific version of localdb would be helpful). — Mark Rotteveel, May 18 '22 at 16:08
What is the data type of the column? A numeric column for example won't store a leading 0. Also, are you not concerned about the length of the number? If you're trying to update mobile numbers with country code 7, don't you want to ignore numbers with country code 72? — Aaron Bertrand, May 18 '22 at 16:10

score 4 · Answer 1 · answered May 18 '22 at 15:56

4

You may use LIKE here:

UPDATE yourTable
SET mobile = '0' + mobile
WHERE mobile LIKE '7%';

answered May 18 '22 at 15:56

Tim Biegeleisen

score 0 · Answer 2 · answered May 18 '22 at 16:00

0

You can write a concat statement

update table
set yourcolumn = CONCAT('0', yourcolumn)
where yourcolumn like '7%'

answered May 18 '22 at 16:00

ArchAngelPwn

2 Answers2