CODEWITHSUNDEEP
sqlgoogle-bigquerylegacy-sql
Shany H.
Shany H.

Reputation: 113

Big query replace a letter in a string by position

I would to like to replace a letter in a string by position in big query. for example XXXXX, YYYYY, ZZZZZ the 5th letter in the string to 0 I've tried to use the Stuff function, but big query doesn't find the function Stuff(XXXXX, 5, 1, '0')

Upvotes: 0

Views: 4032

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Below is for BigQuery Standard SQL and Legacy SQL (works for both) 

SELECT REGEXP_REPLACE(str, r'(.{4}).(.*)', r'\10\2')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Probably the simplest method is more basic string operations:

select concat(substr(x, 1, 4), '0', substr(x, 6))

Upvotes: 2

Related Questions