Regex expression to extract string from text via tsql

Thank you Naveen for your reply.

Here the need is to maintain the values as is ,

the expected output is  as below :

database.confidential.table1
[database].dbo.table2
[database].[confidential].table1
database.restricted.[table3]

 

and with the query you provided, it would never have the brackets. We want the values as is

You can use CHARINDEX and PARSENAME to achive it but the cache here is, somewhere like database or schema or table name you might have [ ] brackets. So excluding the brackets we can get it.

Try the below query:

DECLARE @SQLText NVARCHAR(MAX) = 'Select c1, c2,c3
from  database.confidential.table1 t1
left join [database].dbo.table2 t2 on t1.c=t2.c
left join [database].[confidential].table1 t3 on t3.x=t2.x
inner join database.restricted.[table3] t4 on t4.y=t3.y';


DROP TABLE IF EXISTS #Temp_DBTable
CREATE TABLE #Temp_DBTable (
    DatabaseName NVARCHAR(MAX),
    SchemaName NVARCHAR(MAX),
    TableName NVARCHAR(MAX)
);


INSERT INTO #Temp_DBTable (DatabaseName, SchemaName, TableName)
SELECT DISTINCT
    -- Extract Database Name
    CASE 
        WHEN CHARINDEX('.', t.TableName) > 0
        THEN PARSENAME(t.TableName, 3)
        ELSE NULL
    END AS DatabaseName,
    -- Extract Schema Name
    CASE 
        WHEN CHARINDEX('.', t.TableName) > 0
        THEN PARSENAME(t.TableName, 2)
        ELSE NULL
    END AS SchemaName,
    -- Extract Table Name
    PARSENAME(t.TableName, 1) AS TableName
FROM (
    SELECT 
        LTRIM(RTRIM(value)) AS TableName
    FROM STRING_SPLIT(@SQLText, ' ')
    WHERE LTRIM(RTRIM(value)) LIKE '%.%'
) AS t;


SELECT DatabaseName +'.'+ SchemaName +'.'+ TableName AS Column1 
FROM #Temp_DBTable 
WHERE PATINDEX('%[^a-zA-Z0-9_.]%', DatabaseName +'.'+ SchemaName +'.'+ TableName) = 0   ;

Output returned

To handle the generic case where you want to extract patterns like [abc].xyz.lmn or 12345.[123].[890], you can use a regular expression-based approach. Unfortunately, SQL Server does not have built-in regular expression functions, but you can use a combination of PATINDEX, SUBSTRING, and CHARINDEX to achieve a similar result.

Here is an example T-SQL script:

DECLARE @sqlText NVARCHAR(MAX) = '
This is a poc for [abc].xyz.lmn or 12345.[123].[890]
';

DECLARE @pattern NVARCHAR(100) = '%\[[^\]]*\]([^\s]*\.)+[^\s]*%'; -- Matches patterns like [abc].xyz.lmn

WHILE PATINDEX(@pattern, @sqlText) > 0
BEGIN
    DECLARE @matchStart INT = PATINDEX(@pattern, @sqlText);
    DECLARE @matchEnd INT = CHARINDEX(' ', @sqlText + ' ', @matchStart);
    IF @matchEnd = 0
        SET @matchEnd = LEN(@sqlText) + 1;

    DECLARE @matchedText NVARCHAR(MAX) = SUBSTRING(@sqlText, @matchStart, @matchEnd - @matchStart);
    PRINT @matchedText;

    SET @sqlText = SUBSTRING(@sqlText, @matchEnd + 1, LEN(@sqlText));
END

Thanks

Thank you Naimish for your input but hardcoding on from and Join keyword may not work as the above was just a sample example.

The use case is based on generic for below example as well like 

 

text is :

This is a poc for [abc].xyz.lmn or 12345.[123].[890]

 

So for the sbove text I need the below output:

[abc].xyz.lmn

12345.[123].[890]

 

 

also thank you Jayraj but in your case, not getting the exact output for the 3rd row :

database.restricted.[table3] since the regex is on []

Hi Nandan Hegde,

To extract specific values from the given SQL text using T-SQL, you can use a combination of string manipulation functions such as CHARINDEX, SUBSTRING, and PATINDEX. Here's an example of how you can achieve this:

DECLARE @sqlText NVARCHAR(MAX) = '
Select
c1,
c2,
c3
from
database.confidential.table1 t1
left join [database].dbo.table2 t2 on t1.c=t2.c
left join [database].[confidential].table1 t3 on t3.x=t2.x
inner join database.restricted.[table3] t4 on t4.y=t3.y
'

DECLARE @startIndex INT = 1
DECLARE @endIndex INT = 1
DECLARE @extractedValues TABLE (Value NVARCHAR(MAX))

WHILE @startIndex > 0 AND @endIndex > 0
BEGIN
    SET @startIndex = PATINDEX('%[[]%]%', @sqlText)
    SET @endIndex = CHARINDEX(' ', @sqlText, @startIndex)
    
    IF @startIndex > 0 AND @endIndex > 0
    BEGIN
        INSERT INTO @extractedValues (Value)
        SELECT SUBSTRING(@sqlText, @startIndex, @endIndex - @startIndex)
        
        SET @sqlText = STUFF(@sqlText, @startIndex, @endIndex - @startIndex, '')
    END
END

SELECT Value FROM @extractedValues

The PATINDEX function is used to find the starting index of the desired strings, and the CHARINDEX function is used to find the ending index. We then use the SUBSTRING function to extract the value between the starting and ending indices.

The extracted values are stored in a table variable @extractedValues, and you can retrieve them by selecting from this table.

Please note that this example assumes that the desired values are enclosed in square brackets ([]). If the format of the values is different, you may need to modify the pattern used in the PATINDEX function accordingly.

Try below

DECLARE @sqlText NVARCHAR(MAX) = '
Select
c1,
c2,
c3
from
database.confidential.table1 t1
left join [database].dbo.table2 t2 on t1.c=t2.c
left join [database].[confidential].table1 t3 on t3.x=t2.x
inner join database.restricted.[table3] t4 on t4.y=t3.y
';

DECLARE @startPos INT;
DECLARE @endPos INT;
DECLARE @tableName NVARCHAR(MAX);

-- Loop through the occurrences of "from" and "join" to extract table names
WHILE (CHARINDEX('from', @sqlText) > 0 OR CHARINDEX('join', @sqlText) > 0)
BEGIN
    SET @startPos = NULL;
    SET @endPos = NULL;

    -- Check for "from" or "join" and set start position accordingly
    IF CHARINDEX('from', @sqlText) > 0
        SET @startPos = CHARINDEX('from', @sqlText) + 4;
    ELSE IF CHARINDEX('join', @sqlText) > 0
        SET @startPos = CHARINDEX('join', @sqlText) + 4;

    -- If start position is found, find the end position
    IF @startPos IS NOT NULL
        SET @endPos = CHARINDEX(' ', @sqlText, @startPos);

    -- If end position is found, extract the table name
    IF @endPos IS NOT NULL
    BEGIN
        SET @tableName = SUBSTRING(@sqlText, @startPos, @endPos - @startPos);

        -- Remove leading and trailing square brackets (if any)
        SET @tableName = REPLACE(REPLACE(@tableName, '[', ''), ']', '');

        PRINT @tableName; -- Output the extracted table name
    END

    -- Remove processed part of the SQL text
    SET @sqlText = RIGHT(@sqlText, LEN(@sqlText) - @endPos);
END

Thanks