CODEWITHSUNDEEP
sqlmysql
Naveed
Naveed

Reputation: 1

SQL column to be formatted

I have two columns in MySQL with gpscoordinatelatitude and gpscoordinatelongitude, both as nvarchar. I have different formats for them, like:

GPS Coordinates
N 24 34.124 E 46 38 25.3
N 24 34.7 E 46 33.353
N 24 34.756 E 46 33.594
N 24 34.756 E 46 33.594

I want them in a single format, like

N XX XX XX.XXX E XX XX XX.XXX

Can any one help me to add zeroes wherever I have a single digit after the decimal point?

Upvotes: 0

Views: 131

Answers (1)

rxmnnxfpvg
rxmnnxfpvg

Reputation: 30993

Since your coordinates (like N XX XX XX.XXX) have a fixed length of 14:

UPDATE gps_table 
SET gpscoordinatelatitude = RPAD(TRIM(gpscoordinatelatitude),14,'0'),
    gpscoordinatelongitude = RPAD(TRIM(gpscoordinatelongitude),14,'0');
-- turns N 24 34.7 into N 24 34.700 and
-- turns E 46 33.5 into E 46 33.500

Upvotes: 1

Related Questions