如何在引号之间选择特定数据 (") [英] How to select specific data between Quotes (")
问题描述
我正在重新发布我的问题,因为我是 SQL 2012 的新手.
I am reposting my question as I am new to SQL 2012.
我想获取以下行中引号 (") 之间的数字数据,
I want to fetch the numeric data between quotes (") in the following rows,
row1:'asdalknd,"1,2,3,4",slknsdl,"5,6,7,8",snlsn'
row1:'asdalknd,"1,2,3,4",slknsdl,"5,6,7,8",snlsn'
row2:'asknd,"111,267,387,4756",snsdl,"534,646,767,348",snlssdsdsdsjkvkn'
row2:'asknd,"111,267,387,4756",snsdl,"534,646,767,348",snlssdsdsdsjkvkn'
row3'....
row4'....
row5'....
row6'...
row7'...
row8'....
上面提到的是单列的行.
The above mentioned are the rows of a single column.
我只想提取数字(每行可能在另一列中)
I just want to extract the numerics(may be in another column for each rows)
任何人都可以帮忙,因为这超出了我对 t-sql 的基本知识.
Can anybody pls help, as this is way above my basic knowledge of t-sql.
谢谢
推荐答案
这丑陋,但最终会奏效:
COLUMN = 'jksjdksls#$#$@@kskjfjf,"123,456,789" lsnslkdswfnslsjfls'
<代码>左(右(列,len(列)-instr(列,"")),指令(右(列,len(列)-instr(列,"")),"""") -1)
--> 123,456,789
就是这样:
- 我们取这个字符串
'jksjdksls#$#$@@kskjfjf,"123,456,789" lsnslkdswfnslsjfls'
- 用
instr(COLUMN,"""")
找到的第一次出现" --> 返回 24 - 取字符串的右端.因此我们需要取
len(COLUMN)
--> 55的字符串长度,减去第一个"(24) 的位置 - 然后我们需要在正确的字符串中找到带有
instr()
的第二个 ",我们需要用right(COLUMN,len(COLUMN)-instr(COLUMN,""""))
并减去 1 的 ".
- We take this string
'jksjdksls#$#$@@kskjfjf,"123,456,789" lsnslkdswfnslsjfls'
- find the first occurence of " with
instr(COLUMN,"""")
--> returns 24 - take the right end of the string with. Therefore we need to take the length of the string with
len(COLUMN)
--> 55 and substract the position of the first " (24) - then we need to find the second " with
instr()
in the right string, which we need to create again withright(COLUMN,len(COLUMN)-instr(COLUMN,""""))
and substract 1 for the ".
这篇关于如何在引号之间选择特定数据 (")的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!