Extract value from a String

Question

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

Answer 1

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?

Answer 2

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:

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.

Answer 3

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