CODEWITHSUNDEEP
google-bigquery
thstart
thstart

Reputation: 427

How to replace strings in column with ordered numbers?

I have the following table:

CityNamesZip5Plus4

===========================

Zip5    Plus4   CityName
===========================
36067   1500    Prattville
36067   1501    Prattville
36067   1502    Prattville

48085   3614    Troy
48085   3629    Troy
48085   3640    Troy
48085   3641    Troy

Need to replace CityName with numbers beginning from 1

so the table to look like this:

CityNameNumberZip5Plus4

===========================

Zip5    Plus4   CityNameNumber
===========================
36067   1500    1
36067   1501    1
36067   1502    1

48085   3614    2
48085   3629    2
48085   3640    2
48085   3641    2

The CityName -> CityNameNumber correspondency should be in separate table : CityNamesNumbers

Th question is how to generate CityNamesNumbers table and use it to generate CityNameNumberZip5Plus4?

Upvotes: 0

Views: 70

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Below is for BigQuery Standard SQL

how to generate CityNamesNumbers table ... 

#standardSQL
CREATE OR REPLACE TABLE `project.dataset.CityNamesNumbers` AS
SELECT CityName, ROW_NUMBER() OVER() CityNameNumber 
FROM `project.dataset.CityNamesZip5Plus4`
GROUP BY CityName

... and use it to generate CityNameNumberZip5Plus4? 

#standardSQL
CREATE OR REPLACE TABLE `project.dataset.CityNameNumberZip5Plus4` AS
SELECT x.* EXCEPT(CityName), CityNameNumber
FROM `project.dataset.CityNamesZip5Plus4` x
JOIN `project.dataset.CityNamesNumbers` y
USING(CityName)

Upvotes: 1

Related Questions