Capturing data inside double quotes text

[benchpolo]

I have a series of data rows where I need to capture the text inside the double quotes, but due to the different text length, I'm not quite sure how to do it.

Data:
"INSUFFICIENT INFORMATION" Not enough medical information
"NO MEDICAL NECESSITY" Based on the clinical information submitted
"INSUFFICIENT INFORMATION" Not enough medical information was submitted
"NOT A COVERED BENEFIT" The above noted service is not a benefit covered under
"OUT-OF-NETWORK TO IN-NETWORK" The request for Consultation

Please advise. Thanks.

[Jim Doherty]

I have a series of data rows where I need to capture the text inside the double quotes, but due to the different text length, I'm not quite sure how to do it.

Data:
"INSUFFICIENT INFORMATION" Not enough medical information
"NO MEDICAL NECESSITY" Based on the clinical information submitted
"INSUFFICIENT INFORMATION" Not enough medical information was submitted
"NOT A COVERED BENEFIT" The above noted service is not a benefit covered under
"OUT-OF-NETWORK TO IN-NETWORK" The request for Consultation

Please advise. Thanks.

Looking at the data as is, the logic would be to capture the position in each of the strings of the last speechmark and cut from the data all text to the left of it leaving you with the data in speechmarks. The next step would then be to simply replace the speechmarks leaving you with the data.

Below is a user defined function that captures the numerical position of the last character defined as the parameter for @char when examining the parameter @string that is passed to it. Using the LEFT function then in any view, the logic is to return the characters upto the position of the last speechmark and then wrap the lot in a REPLACE function to remove the speechmarks fore and aft. We thus end up with a column of data called 'My Stripped Data' holding the values you require

Your method of calling the function to achieve this is based around the assumption that you have a table called TABLE1 and that there is a field called simply 'Mydata' and that in that Mydata field has many rows of the data you have outlined

Your SQL syntax for the view would be this

SELECT Mydata, REPLACE(LEFT(Mydata, dbo.GetLastChar(Mydata, '"')), '"', '') AS [My Stripped Data]
FROM dbo.TABLE1

The above SQL will return two columns of data namely your original data and a column to the right of it containing the data you require.

The below is the user defined function

 
Create function dbo.UDF_GetLastChar(@string varchar(8000), @char char)
Returns INT
As
Begin
        If isnull(@string, '') = '' OR isnull(@char, '') = ''
        Return 0 
declare @charpos int
select @charpos = len(@string) - charindex(@char, reverse(@string)) + 1
If @charpos > len(@string) 
Select @charpos = 0
Return @charpos
end
 

Regards

Jim :)

[benchpolo]

Jim, thanks that works..