SQL Server - Extract specific value from string.

Extract specific values from given string.

For example, i have some data as follows in LAB table.

Value

LDL=86

HDL-48  LDL-108

CHO=235  LDL=135  TRI=237

HDL=45  LDL=134

HDL=82  LDL=99 N

CHO=259  LDL=157  TRI=282

LDL:  123

See scanned report.  CHOLEST: 211  LDL: 211  TRIG: 208  HDL DIR: 56  VLDL: 42

Want to extract only LDL values from above and show it as follows.

LDL=86	86
HDL-48  LDL-108	108
CHO=235  LDL=135  TRI=237	135
HDL=45  LDL=134	134
HDL=82  LDL=99 N	99
CHO=259  LDL=157  TRI=282	157
LDL:  123	123
See scanned report.  CHOLEST: 211  LDL: 211  TRIG: 208  HDL DIR: 56  VLDL: 42	211

Written sample T-SQL code to extract required data. 

Script:

CREATE FUNCTION [dbo].[parseint]

(@string VARCHAR(256))

RETURNS VARCHAR(256)

AS

BEGIN

declare @p_string varchar(256)

set @p_string = substring(@string,CHARINDEX('LDL',replace(upper(@string),'VLDL','VXDL')),len(@string))

DECLARE @isalpha INT

SET @isalpha = PATINDEX('%[^0-9]%', @p_string)

BEGIN

WHILE @isalpha > 0

BEGIN

SET @p_string = STUFF(@p_string, @isalpha, 1, '' )

SET @isalpha = PATINDEX('%[^0-9]%', @p_string )

if @isalpha<>1 

break

END

END

RETURN coalesce(substring(@p_string,1,(case when @isalpha=0 then 3 else (@isalpha-1) end)),0)

END

Calling above function:

SELECT value,

                dbo.parseint(value) as LDL 

FROM dbo.lab

Output: