Join on number in a string

[JunkDrawer]

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 = ?)

[ms4446 · Accepted solution]

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.

[JunkDrawer]

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)