Join on number in a string

Google Cloud Database
1

I am trying to join a number with a field that has a string field with letters, spaces, special character and a number.

field_1 12345

fiield_2 letter: 12345 (this field can have 4 numbers or 6 numbers after the :{space})

join {table} as {table_1} ON (field_1 = ?)

2

Joining a numeric field with a string field in SQL, especially when the string field contains various characters and a number, requires a bit of manipulation. In your case, since field_2 contains a pattern like “letter: 12345”, and you want to join it with field_1 which is a number, you’ll need to extract the numeric part from field_2.

Assuming you are using Google Cloud SQL with a MySQL or PostgreSQL database, here’s how you can approach it:

For MySQL:

You can use the REGEXP_SUBSTR function to extract the number from field_2. The query would look something like this:

SELECT *
FROM {table} AS table_1
JOIN {another_table} AS table_2
ON table_1.field_1 = CAST(REGEXP_SUBSTR(table_2.field_2, '[0-9]+') AS UNSIGNED)

This query extracts the sequence of digits from field_2 and casts it to an unsigned integer for comparison with field_1.

For PostgreSQL:

PostgreSQL uses a similar approach but with different functions:

SELECT *
FROM {table} AS table_1
JOIN {another_table} AS table_2
ON table_1.field_1 = CAST(SUBSTRING(table_2.field_2 FROM '[0-9]+') AS INTEGER)

Here, SUBSTRING with a regular expression is used to extract the number, and then it’s cast to an integer.

1 Like
3

Thank you so much.

I had to modify your MYSQL a bit

JOIN {table} AS table_1
ON table_2.field = CAST(REGEXP_SUBSTR(table_1.field ‘[0-9]+’) AS INT64)