Extract value from a String

sourav dutta 211 Reputation points
2021-08-06T05:47:08.377+00:00

Hello,
I attach a pdf file where I have to extract the value from a string with the result set.

Please help.

Thanks in Advance.121047-docs.pdf

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,894 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,564 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Olaf Helper 41,331 Reputation points
    2021-08-06T05:57:18.677+00:00

    You tagged your post with SQL Server; in which way is it related to? Are you looking for a Transact-SQL solution.
    If you have installed an IFilter for PDF, then you can use Full Text Search to index the PDF file, see Full-Text Search

    Or are you looking for an other solution/way?


  2. EchoLiu-MSFT 14,571 Reputation points
    2021-08-06T07:14:18.583+00:00

    Hi @sourav dutta ,

    Please check:

    CREATE TABLE #test(id int, [name] char(55))  
    INSERT INTO #test VALUES(1,'Import-Asuri-598')  
    ,(2,'Import-HPE-1045-Upload'),(3,'Import-Alti-1023(Pend)')  
    ,(4,'Com-Asuri-948(1:30PM)'),(5,'Import-connect pid 1063')  
    ,(6,'Import-JumpStart-PID-1051(irhythm)'),(7,'Import-Com-1071/1072')  
      
    CREATE FUNCTION [dbo].[GET_NUMBER] (@S VARCHAR(100))  
    RETURNS VARCHAR(100)  
    AS  
    BEGIN  
         WHILE PATINDEX('%[^0-9\\.\\^0-9]%', @S) > 0  
        BEGIN  
        SET @S = STUFF(@S, PATINDEX('%[^0-9\\.\\^0-9]%', @S), 1, '');  
        END;  
        RETURN @S;  
    END  
      
      
    ;WITH cte  
    as(SELECT *,  
    CASE WHEN CHARINDEX('(',[name])>0 THEN  
    SUBSTRING([name],CHARINDEX('(',[name]),LEN([name])-CHARINDEX('(',[name])+1)  
    WHEN PATINDEX('%[0-9]/[0-9]%',[name])>0 THEN  
    SUBSTRING([name],PATINDEX('%[0-9]/[0-9]%',[name])+2,LEN([name])-PATINDEX('%[0-9]/[0-9]%',[name])+1)  
    END name2  
    FROM #test)  
    ,cte2 as(SELECT *,[dbo].[GET_NUMBER]([name]) n1,  
    [dbo].[GET_NUMBER](name2) n2  
    FROM cte)  
    SELECT id,[name],CASE WHEN n2 IS NOT NULL  
    THEN REPLACE(n1,n2,'')  
    ELSE [n1] END [Output] FROM cte2  
    UNION ALL  
    SELECT id,[name],n2  
    FROM cte2  
    WHERE PATINDEX('%[0-9]/[0-9]%',[name])>0  
      
    DROP FUNCTION [dbo].[GET_NUMBER]   
    

    Output:
    121125-image.png

    If you have any question, please feel free to let me know.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.


  3. EchoLiu-MSFT 14,571 Reputation points
    2021-08-06T08:22:50.993+00:00

    Your data has no fixed rules, so my data only applies to the data you provide. But I guess, the following code can solve your problem:

    ;WITH cte
    as(
    SELECT *,
    LEFT(SUBSTRING([name],PATINDEX('%[0-9]%',[name]),10),
    PATINDEX('%[^0-9]%',SUBSTRING([name],PATINDEX('%[0-9]%',[name]),10))-1)[Output]
    ,CASE WHEN PATINDEX('%[0-9]/[0-9]%',[name])>0
    THEN SUBSTRING([name],PATINDEX('%[0-9]/[0-9]%',[name])+2,10) END n1
    FROM #test)
    ,cte2 as(SELECT id,[name],[Output],
    CASE WHEN n1 IS NOT NULL
    THEN LEFT(n1,PATINDEX('%[^0-9]%',n1)-1) END [Output2]
    FROM cte)
    
    SELECT id,[name],[Output] FROM cte2 
    UNION ALL
    SELECT id,[name],[Output2] FROM cte2 
    WHERE [Output2] IS NOT NULL
    
    0 comments No comments