T-SQL Substring Regex?

Question

I have a problem extracting substrings. I'm logging some SQL queries in my database and need to filter that.

Possible strings can look like these 2 examples:

1. [DatabaseName].[dbo].[TableName$TableExtra]
2. "DatabaseName".dbo."TableName$TableExtra"

I've tried this:

DECLARE @c AS NVARCHAR(MAX) =
'
"DatabaseName".dbo."TableName$TableExtra"
'

SELECT 
    SUBSTRING(SUBSTRING(@c, PATINDEX('%"DatabaseName".dbo."%', @c) + 20, 100), 1,
                        PATINDEX('%"%', SUBSTRING(@c, PATINDEX('%"DatabaseName".dbo."%', @c) + 20, 100)) - 1)

That will only work with the second example but with the first one.

Can anyone help me how to write the filter that works for both samples?

Have a nice day

While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Jul 9, 2021 at 13:08
If you are trying to say that you will be processing complete queries, e.g. a common table expression with 42 joins and 13 comments, and extracting the referenced table name(s) then you need to be looking for a TSQL parser. — HABO
– HABO, Commented Jul 9, 2021 at 17:36

HoneyBadger · Accepted Answer · 2021-07-09 08:34:48Z

1

If you want to extract tablenames etc, you can use PARSENAME():

DECLARE @c AS NVARCHAR(MAX) ='"DatabaseName".dbo."TableName$TableExtra"'

SELECT PARSENAME(@c, 1) TableName
,       PARSENAME(@c, 2) SchemaName
,       PARSENAME(@c, 3) DatabaseName

Results in:

+----------------------+------------+--------------+
|      TableName       | SchemaName | DatabaseName |
+----------------------+------------+--------------+
| TableName$TableExtra | dbo        | DatabaseName |
+----------------------+------------+--------------+

answered Jul 9, 2021 at 8:34

HoneyBadger

15.2k3 gold badges36 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RoxoR Over a year ago

Hey HoneyBadger Thank you but its not exactly what im looking for. The nvarchar normally containts different sized sql querys and i need to find the both examples to extract only the Tablename of it

Collectives™ on Stack Overflow

T-SQL Substring Regex?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related