Return regex match from filtered SQL Query as a field

Question

I need to query some information based on a Regex Match. But besides filtering the information by using regex, I also want to show the matched content as a new field.

Example:

DECLARE @example TABLE ( field VARCHAR(100) )

INSERT INTO @example VALUES (' generic info A #123# ')
INSERT INTO @example VALUES (' chewbacca #778# info B ')
INSERT INTO @example VALUES (' do not retrieve ')
INSERT INTO @example VALUES (' #456# balbalba ')

SELECT * FROM @example WHERE field LIKE '%[0-9][0-9][0-9]%'

My query returns:

|field
|-----------------------------
| generic info A #123# 
| chewbacca #778# info B 
| #456# balbalba 

And I would like to know if there's a way of getting the matched info in another field, as this example:

|match |field
|------|-----------------------------
|#123# | generic info A #123# 
|#778# | chewbacca #778# info B 
|#456# | #456# balbalba 

Thanks a lot!

Answer 1

The answer is using substring and patindex functions like so (in other words use patindex function to get first position of mathced string and then "cut" the characters needed using substring):

DECLARE @example TABLE ( field VARCHAR(100) )

INSERT INTO @example VALUES (' generic info A #123# ')
INSERT INTO @example VALUES (' chewbacca #778# info B ')
INSERT INTO @example VALUES (' do not retrieve ')
INSERT INTO @example VALUES (' #456# balbalba ')

SELECT e.field, SUBSTRING(E.field, patindex('%[0-9][0-9][0-9]%', e.field)-1, 5) as match
FROM @example e
WHERE e.field LIKE '%[0-9][0-9][0-9]%'