Extracting Data from a String

Transact-SQL
1

I need some help with using Substring, CharIndex, PatIndex, etc. I need a query that will extract data from a string, where the data is between two quotes ("......"), but the data may not be the same length. For example

Table Name: LettersofAlphabet
This is one column called Alphabets
"LettersofAlphabet":"abcdefghi"
"LettersofAlphabet":"defghijklmnopqrs"
"LettersofAlphabet"."abcdefghijklmnopqrstuv"

For each row I need to extract the actual alphabets sitting between the quotes. The first part of the column ("LettersofAlphabet") is always the same length, but the actual alphabets are different lengths.

How do I extract the string when the data I want is different lengths? Please help

Thanks

2

hi

You could create a function an do it...

Is that okay with you ?

3

Oh yes that's fine; I just need any method to extract the correct data.

4 
SELECT Alphabets,
    SUBSTRING(Alphabets, start_of_string, 
        CHARINDEX('"', Alphabets, start_of_string) - start_of_string)
FROM LettersofAlphabet
CROSS APPLY (
    SELECT CHARINDEX(':"', Alphabets) + 2 AS start_of_string
) AS ca1
5

Scott,

Thanks for the reply. I think I might have mislead with only part of my problem. The actual rows of the table have more data in it, so that's why I was having problems extracting the data.

Table Name: LettersofAlphabet
This is one column called Alphabets
"LettersofAlphabet":"abcdefghi":"123456789":"thisisatest"
"LettersofAlphabet":"defghijklmnopqrs":"123456789":"thisisatest"
"LettersofAlphabet"."abcdefghijklmnopqrstuv":"123456789":"thisisatest"

Does your code work the same with the change in the table. While every other piece of data is the same length, again the data that I need is not the same length. I apologize again, for not giving the full story.

Thanks,

6

Yes, my code above works fine with that data, IF the starting delimiter is consistent, :".

If it could be colon double-quote OR period dbl-quote, rather than always colon dbl-quote, then my code will need adjusted to allow for that.

SELECT Alphabets,
    SUBSTRING(Alphabets, start_of_string, 
        CHARINDEX('"', Alphabets, start_of_string) - start_of_string)
FROM (VALUES
    ('"LettersofAlphabet":"abcdefghi":"123456789":"thisisatest"'),
    ('"LettersofAlphabet":"defghijklmnopqrs":"123456789":"thisisatest"'),
    ('"LettersofAlphabet":"abcdefghijklmnopqrstuv":"123456789":"thisisatest"')
) AS LettersofAlphabet(Alphabets)
CROSS APPLY (
    SELECT CHARINDEX(':"', Alphabets) + 2 AS start_of_string
) AS ca1