Change the first character in the field

Question

I want to change the first character from “U” to “S” in a field in my table (MYSQL), so far I have the following:

UPDATE customers_basket SET products_id = REPLACE( LEFT(products_id,1), 'U', 'S') + SUBSTRING(products_id, 2, CHAR_LENGTH(products_id) ); 

but this does not give me the desired effect, can someone help me?

Thank you!

Answer 1

 UPDATE customers_basket SET products_id = CONCAT( REPLACE( LEFT(products_id,1), 'U', 'S'), SUBSTRING(products_id, 2, CHAR_LENGTH(products_id) )); 

You are trying to add characters together, for example.

 select 'c' + 'a'; +-----------+ | 'c' + 'a' | +-----------+ | 0 | +-----------+ 

Answer 2

 update customers_basket set products_id = 'S' + SUBSTRING(products_id,2,CHAR_LENGTH(products_id)) where LEFT(products_id,1) = 'U' 

Answer 3

You can simply use:

 UPDATE customers_basket SET products_id=CONCAT('S', SUBSTRING(products_id FROM 2)); 

ie: Instead of replacing the initial “U” with “S”, just start with “S” and copy the remaining characters.

This, of course, assumes that all products_id entries begin with the letter “U”. If they do not, just add the WHERE clause, for example:

 UPDATE customers_basket SET products_id=CONCAT('S', SUBSTRING(products_id FROM 2)) WHERE LEFT(products_id, 1) = 'U'; 

Answer 4

 update customers_basket set products_id = CONCAT('S', RIGHT(products_id,CHAR_LENGTH(products_id)-1)) WHERE LEFT(products_id,1) = 'U' 

Answer 5

SUBSTRING(products_id, 2) will give you the second character forward. So you can do something like:

 update customers_basket set products_id = concat( 'S', substring(products_id, 2)); 

If you want to change only those whose first character is "U", just add the appropriate where clause, for example

 update customers_basket set products_id = concat( 'S', substring(products_id, 2)) where left(products_id,1)='U';