I need to remove a prefix 0 from the data in specific column in BQ. How it should be done? Using substr function?

Example:

and the output should be without zeros, so should be like:

in all listed cases above.

I know how to add a prefix:

UPDATE
  table.name
SET
 id = CAST(CONCAT('99999', CAST(id AS STRING)) AS INTEGER)
WHERE
  code = 'US' and cast(id as string)

CodePudding user response：

If you cast your string to integer, you'll remove the zeroes. Then in order to get back your string, it's sufficient to cast back to string.

UPDATE table.name
SET id = CAST(CAST(id AS INTEGER) AS STRING)
WHERE code = 'US'

Does it work for you?

CodePudding user response：

The below query should work

SELECT REPLACE(LTRIM(REPLACE('00012345', '0', ' ')),' ', '0');
SELECT REPLACE(LTRIM(REPLACE('0012345', '0', ' ')),' ', '0');

CodePudding user response：

Using LTRIM,

SELECT LTRIM(id, '0') id 
  FROM UNNEST(['012345', '0012345', '00012345']) id

Output will be: