question

souravdutta-2069 avatar image
0 Votes"
souravdutta-2069 asked $$ANON_USER$$ commented

Extract value from a String

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-generalsql-server-transact-sql
docs.pdf (395.5 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Next time you have a problem like this, rather than attaching a PDF - which caused some confusion - provide the sample data as CREATE TABLE + INSERT statements. This makes it easier for people who want to assist you, as they can copy and paste into a query window directly.

1 Vote 1 ·

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered OlafHelper-2800 commented

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?

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes. I am looking for a Transact-SQL solution.

0 Votes 0 ·

Then see the FTS link in my previous post.

0 Votes 0 ·

How Can I do this using substring() charindex() patindex()?

0 Votes 0 ·

How Can I do this using substring() charindex() patindex()?

You can not. A PDF is mainly a binary, not plain text. The PDF IFilter can read PDF and index the found words.





0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered $$ANON_USER$$ commented

Hi @souravdutta-2069,

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.




image.png (9.7 KiB)
· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello,

Where is the function "[dbo].[GET_NUMBER8]"?

Thanks in Advance.

0 Votes 0 ·

Sorry, I forgot to provide it. I updated the answer, please check if it works.

1 Vote 1 ·

Also, If the column have the text like "Import - HPE - 1045 - IMF02 Carrier assign Report" and "Import - Com - 1071/1072 (Business Rules Only)" then the output should be like "1045", "1071" and "1072".

0 Votes 0 ·

But what is the rule to follow?
For example, for Id4 why do you exclude the numbers in "(1:30)"?

Your request is ambiguous.

0 Votes 0 ·

Yes. I have to exclude the numbers in "(1:30)".

0 Votes 0 ·

Hello,

It's working perfectly with the shared data.

But assume I have name "Import - HPE - 1045 - Job 05 - RMA EM" and the output is coming now 104505. But I need 1045 only.

Other wise all good.

0 Votes 0 ·

Also, If the column have the text like "Import - HPE - 1045 - IMF02 Carrier assign Report" and "Import - Com - 1071/1072 (Business Rules Only)" then the output should be like "1045", "1071" and "1072".

0 Votes 0 ·

This value is different: "Import - Connect Pid 1063".

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.