CODEWITHSUNDEEP
sqlgoogle-bigquery
MrGinger
MrGinger

Reputation: 17

Is there a numeric version of SUBSTR in BigQuery?

This may be a complete noob question. I have spent hours looking for a solution but haven't found one...

I am trying to capture the first digit of each number in a column of numbers. For example:

173563 = 1
247309 = 2
653638 = 6

etc

I know that I could do this using SUBSTR if the value were a string but that doesn't work for numbers. I have a work around but there must be a better way than how I'm currently doing it.

Any help would be much appreciated

with
-- Convert to string and get the first character
stage1 as (
SELECT
number
, SUBSTR(CAST(number AS STRING), 1, 1) AS cut_off
FROM my_table
),

-- Convert back to a numeric value
stage2 as (
SELECT
number
, CAST(cut_off AS NUMERIC) AS cut_off,
FROM stage1
)

select
number,
cut_off
from stage2

Upvotes: 1

Views: 2295

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173036

You can use below "trick"

with `project.dataset.table` as (
  select 173563 number union all
  select 247309 union all
  select 653638 
)
select number, left('' || number, 1) cut_off
from `project.dataset.table`

with output

enter image description here

Obviously, such a trick allows you to use any string function to meet your needs

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270181

You can do this all in one expression:

CAST(LEFT(CAST(number AS STRING), 1) as int64)

If the length of the number is always 6 digits, you can use arithmetic:

FLOOR(number / 100000)

Or:

DIV(number, 100000)

Upvotes: 0

Related Questions