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
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
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.
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!
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?
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.
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.
Hello,
Where is the function "[dbo].[GET_NUMBER8]"?
Thanks in Advance.
Sorry, I forgot to provide it. I updated the answer, please check if it works.
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".
But what is the rule to follow?
For example, for Id4 why do you exclude the numbers in "(1:30)"?
Your request is ambiguous.
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.
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".
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
19 people are following this question.
Year and Month aggregation in same Pivot table in SQL Server
SQL Server Query for Searching by word in a string with word breakers
How to show first row group by part id and compliance type based on priorities of Document type?
Query to list all the databases that have a specific user
T-sql query to find the biggest table in a database with a clustered index