| Body |
|---|
| /tpt/TpScaning-ScaningRoad-PERSIVEE-202301020730327429-1567651.tp |
| /tpt/TpScaning-BaliResortsInterns-PERSIVEE-202205300341154744-909890.tp |
| /tpt/TpScaning-RXX-PERSIVEE-202108210412090110-000989.tp |
| /tpt/TpScaning-Backnationnotecom-PERSIVEE-202112201229124214-783672.tp |
| /tpt/TpScaning-DBZ-PERSIVEE-202109131129036172-908954.tp |
| /tpt/TpScaning-DBZ-PERSIVEE-202109131129036172-17892578.tp |
I have a column 'Body' in a BigQuery table xyz which contains the Scans being requested on our internal app. A Body field contains date part within it i.e. (202301020730327429, 202205300341154744 ...) the 18 digit numbers.
I want to extract this 18 digit sequence in the separate column named date to know when the scan requests were created.
I tried to use the substr function but it did not work correctly as the length of Body field is varying. Is there a better way to get this implemented?
Expected output:
| date |
|---|
| 202301020730327429 |
| 202205300341154744 |
| 202108210412090110 |
| 202112201229124214 |
| 202109131129036172 |
| 202109131129036172 |
CodePudding user response：
You might consider below.
WITH sample_table AS (
SELECT '/tpt/TpScaning-ScaningRoad-PERSIVEE-202301020730327429-1567651.tp' body UNION ALL
SELECT '/tpt/TpScaning-BaliResortsInterns-PERSIVEE-202205300341154744-909890.tp' body UNION ALL
SELECT '/tpt/TpScaning-RXX-PERSIVEE-202108210412090110-000989.tp' body UNION ALL
SELECT '/tpt/TpScaning-Backnationnotecom-PERSIVEE-202112201229124214-783672.tp' body UNION ALL
SELECT '/tpt/TpScaning-DBZ-PERSIVEE-202109131129036172-908954.tp' body UNION ALL
SELECT '/tpt/TpScaning-DBZ-PERSIVEE-202109131129036172-17892578.tp' body
)
SELECT *, PARSE_TIMESTAMP('%Y%m%d%H%M