Certain patterns of PATINDEX search truncates at 2 digits 1

[waubain]

I am trying to extract the first numeric value to the right of the pattern LDL. I am trying to learn about SUBSTRING, PATINDEX and CHARINDEX. I am getting close, but in certain patterns(1 and 8 below) the return value truncates at 2 digits no matter the number of numeric digits.

Where am I going wrong? Stringing all the SUBSTRING and PATINDEX together gets confusing. I am open alternative SQL statements. As a side learning point, is there a best practice on how to wrap the statements to make it more readable?

IF OBJECT_ID('TEMPDB..#demo') IS NOT NULL DROP TABLE #demo
CREATE TABLE #demo(SubjectID int, TextResult varchar(100))
INSERT INTO #demo VALUES 
			(1, 'HDL is 30 and LDL 123')
                       ,(2, 'go find it yourself')
                       ,(3, '231')
                       ,(4, 'HDL = 5.4 (LDL 333)')
                       ,(5, NULL)
                       ,(6, 'LDL: 132, Trigs=140')
			,(7, 'His LDL one year ago was 120')
			,(8, 'HDL 30 LDL 3210')

SELECT 
    SubjectID
    ,LEFT(SUBSTRING(SUBSTRING([TextResult], PATINDEX('%LDL%', [TextResult]), 8000), PATINDEX('%[0-9.-]%', SUBSTRING([TextResult], PATINDEX('%LDL%', [TextResult]), 8000)), 8000), PATINDEX('%[^0-9.-]%', SUBSTRING([TextResult], PATINDEX('%[0-9.-]%', [TextResult]), 8000) + 'X') -1) AS [LDL Text Value]
    --,CAST(LEFT(SUBSTRING(SUBSTRING([TextResult], PATINDEX('%LDL%', [TextResult]), 8000), PATINDEX('%[0-9.-]%', SUBSTRING([TextResult], PATINDEX('%LDL%', [TextResult]), 8000)), 8000) + 'X', PATINDEX('%[^0-9.-]%', SUBSTRING([TextResult], PATINDEX('%[0-9.-]%', [TextResult]), 8000) + 'X') -1) AS float) AS [LDL Numeric Value]
FROM #demo

Thank you in advance.

You don't know what you don't know...

 

[SkipVought]

Build your search string one search value at a time, you'll see a pattern emerge that will help in building each element.

 

[gmmastros]

First, I would suggest that you read this:

http://blogs.lessthandot.com/index.php/datamgmt/datadesign/extracting-numbers-with-sql-server/

In that blog I wrote, there is a user defined function named GetNumbers. This function will return the first number it finds in a string. Clearly, this is not good enough for what you want. To accomplish this, you'll need to first extract the part of the string immediately following "LDL" so that the LDL value is the first number in the string.

After creating the function, you could try this:

SELECT 
    SubjectID,
    dbo.GetNumbers(Case When CharIndex('LDL', TextResult) > 0
		 Then Right(TextResult, Len(TextResult) - CharIndex('LDL', TextResult))
		 Else '' 
		 End) As LDL_Value
FROM #demo

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[waubain]

Thanks Skip for the prod. That is how I got it this far, but I got lazy late last night and just plugged in the LEFT part from an internet example I found. I reworked the LEFT length part and it works.

SELECT 
    SubjectID
    ,LEFT(SUBSTRING(SUBSTRING([TextResult], PATINDEX('%LDL%', [TextResult]), 8000), PATINDEX('%[0-9.-]%', SUBSTRING([TextResult], PATINDEX('%LDL%', [TextResult]), 8000)), 8000), PATINDEX('%[^0-9.-]%', SUBSTRING(SUBSTRING([TextResult], PATINDEX('%LDL%', [TextResult]), 8000), PATINDEX('%[0-9.-]%', SUBSTRING([TextResult], PATINDEX('%LDL%', [TextResult]), 8000)), 8000) + 'X' ) -1 ) AS [LDL Text Value]
FROM #demo

This solution seems quite lengthy. Is there a better way to have written this and returned the same values?

Thanks again.

You don't know what you don't know...

 

[waubain]

Thank you George.
I was replying to Skip and did not see your post before I submitted. I think I started with your example but not as a function and then tried to add to that to extract the number after LDL.

You don't know what you don't know...