CODEWITHSUNDEEP
sqlsql-serverexcel
Bryuk
Bryuk

Reputation: 3345

How to update all records in the table that has no 0 at the beginning on SQL Server?

In my table I have column that contains string with numbers. I need every string be next format: 

5 numbers DOT 4 numbers
xxxxx.xxxx

Right now some of my numbers has 4 numbers DOT 4 numbers, because numbers were imported from one excel file to another excel file and then in the database, and excel has cut 0 at the beginning of some numbers, so I have:

08652.1501
08652.3000
08652.3000
1743.3017
7825.1001
1744.8200
04279.1010

But I need:

08652.1501
08652.3000
08652.3000
01743.3017
07825.1001
01744.8200
04279.1010

How to update my table? Check if string has format xxxx.xxxx then add 0 at the beginning?

Upvotes: 1

Views: 62

Answers (2)

Ghazanfar Mir
Ghazanfar Mir

Reputation: 3543

Assuming your field is varchar following should work..

UPDATE table SET field = (CONCAT('0', field )) WHERE field < 10000;

Upvotes: 0

MichaelMilom
MichaelMilom

Reputation: 3067

UPDATE numberTable
SET number = '0' + number
WHERE LEN(number) = 9

is probably the simplest (assuming that the numbers are missing exactly on leading zero. A more general case could follow the same pattern for two missing zeroes, three missing zeroes, etc.

Upvotes: 4

Related Questions