Please help me with T-SQL query in SQL 2008 to get value from between first double quote to second double quote. The string will have many double quotes. The XXX will be of variable length.
E.g. it will return [email protected] Column
XXX Usr="[email protected]" zone="fyrkkk="0" htyy"ukbpfrttt897="009" /
Many thanks.
Use CHARINDEX to find the double quotes and SUBSTRING to get the string out
This look like only wrong XML syntax for me.
DECLARE @XMLData XML = N'<Node Usr="[email protected]" zone="" fyrkkk="0" htyy="" ukbpfrttt897="009" />'
SELECT tab.col.value('@Usr','NVARCHAR(128)') AS UserMail
FROM @XMLData.nodes('/Node[1]') AS tab(col)
You need a combo of CHARINDEX() to find location of quotes and SUBSTRING() to select the portion of string you want, once you know these indexes. Unfortunately CHARINDEX() gives the FIRST occurence, so this is gonna take some work.
SELECT SUBSTRING(column, CHARINDEX('"', column), CHARINDEX('"', SUBSTRING(column, CHARINDEX('"', column), 100)))
To understand this better, replace CHARINDEX('"', column) with q1 (index of first quote)
SELECT SUBSTRING(column, q1, CHARINDEX('"', SUBSTRING(column, q1, 100)))
Take substring of column from q1 to (index the first occurence of " in everything after q1). 100 can be any integer longer than the max length of that email, I jsut wanted to ensure the second quote is included and theres no problem going over. Play with this, there might be an off-by-one error
SUBSTRING(word, start, length) http://msdn.microsoft.com/en-us/library/ms187748.aspx CHARINDEX(word, char)
