CODEWITHSUNDEEP
mysqlsqlsql-update
Ered
Ered

Reputation: 497

add a digit Into Existing Column

hi im trying to add a digit to a SQL row using this code:

SELECT

      RIGHT( '0900000000' + LTRIM( RTRIM( [Leading_Zero_Field_Name] ) ), 10 )

FROM

      [Table_Name]

in the row numbers are formatted with 9 digts like this:

098928000
097652183
098037207

what i want to accomplish is to add an extra 9 after the first 0 it should be a total of 10 digits and should look like this:

0998928000
0997652183
0998037207

now the problem is when i apply the code above like this:

UPDATE

      [Table_Name]

SET

      [Leading_Zero_Field_Name] = RIGHT( '0900000000' + LTRIM( RTRIM( [Leading_Zero_Field_Name] ) ), 10 )

i get this result:

998928000
997652183
998037207

i don't know why the query deletes the first 0 btw i the type of the row is VARCHAR i also tried with TEXT with the same result.

Upvotes: 0

Views: 2494

Answers (2)

Andriy M
Andriy M

Reputation: 77707

SQL Server has this function called STUFF(). It lets you, among other things, insert a string into another string at a specific position.

In your case, returning results with a 9 inserted at the 2nd position of every value would look like this:

SELECT STUFF(ColumnName, 2, 0, '9') AS NewValue
FROM TableName;

If you really want a MySQL solution, as the tags seem to suggest, use the INSERT() function, whose set of parameters is identical to that of STUFF().

Upvotes: 0

Ulises
Ulises

Reputation: 13429

The RIGHT function uses as many characters from the 1st argument as needed. Your existing numbers don't need that many, just a few so change:

RIGHT( '0900000000' + LTRIM( RTRIM( [Leading_Zero_Field_Name] ) ), 10 )

TO

SELECT RIGHT( '09' + LTRIM( RTRIM( Leading_Zero_Field_Name) ), 10 )

Edited: Using your input provided:

SELECT RIGHT( '09' + LTRIM( RTRIM( 098928000) ), 10 )

Results

0998928000

Does your input change in length?

Upvotes: 1

Related Questions