I have some data stuck in a table. I'll give you an example
codcliente | first name 010 juan 011 pepe 012 Luis
I need to remove the zero in front of each client code. I'm doing it on pgadmin and I can not find anything that can help me. I've been looking at how to treat strings with sql but I do not see anything related to removing the zeros.
Thank you.
You can use a combination of the functions REPLACE and LTRIM of SQL, something like this:
SELECT REPLACE(LTRIM(REPLACE(tuCadena, '0', ' ')),' ', '0')
The Replace function just replaces one string with another one you specify. For example, if I have the string "SQL Tutorial and I want to form" HTML Tutorial ", I would do something like this:
SELECT REPLACE('SQL Tutorial', 'SQL', 'HTML');
Where the first parameter is the complete string, the second what I want to replace, and the third what I want to insert.
Then, the LTRIM function simply removes the spaces to the left of a string.
Understanding all this and returning to your case:
SELECT REPLACE(LTRIM(REPLACE(tuCadena, '0', ' ')),' ', '0')
The REPLACE most internal modifies all 0 by spaces. Then I delete only the spaces at the beginning with LTRIM , and then I replace the spaces that were left with zeros.
If you castrate the entire code, it will auto-zero to the number without the 0
SELECT CAST('010' AS int) -->10
SELECT CAST('00120' AS int) -->120
SELECT CAST('00000107' AS int) -->107