sql server 根据某个字符串将一行转为一列

转载
网络安全卫士 2024-12-11 13:48:20
文章标签 oracle ^]字符字符串 bc 正则表达式 文章分类 SQL Server 数据库
1、替换
TRANSLATE(string,from_str,to_str)
返回将(所有出现的)from_str中的每个字符替换为to_str中的相应字符以后的string。TRANSLATE 是 REPLACE 所提供的功能的一个超集。如果 from_str 比 to_str 长，那么在 from_str 中而不在 to_str 中的额外字符将从 string 中被删除，因为它们没有相应的替换字符。to_str 不能为空。Oracle 将空字符串解释为 NULL，并且如果TRANSLATE 中的任何参数为NULL，那么结果也是 NULL。
简单说就是，从左到右一一对应的关系，如果不能对应，则视为空值。
字符一一对应的情况
SELECT TRANSLATE('abcdefghij','abcdef','123456') FROM dual;
TRANSLATE (
--------------
123456ghij
字符不一一对应的情况
SELECT TRANSLATE('abcdefghij','abcdefghij','123456') FROM dual;
TRANSL
----------
123456
select translate('abcbbaadef','ba','#@') from dual　(b将被＃替代，a将被＠替代)
select translate('abcbbaadef','bad','#@') from dual　(b将被＃替代，a将被＠替代，d对应的值是空值，将被移走)
因此：结果依次为：@#c##@@def 和@#c##@@ef
示例一：将数字转换为9，其他的大写字母转换为X，然后返回。
SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX')
FROM DUAL
示例二：将数字保留，将其他的大写字母移除。
SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'0123456789')
FROM DUAL
示例三：示例证明是按照字符来处理，不是按照字节来处理，如果to_string的字符数比from_string多的话，多出的字符数似乎没有什么用处，也不会引发异常。
SELECT TRANSLATE('我是中国人,我爱中国', '中国', 'China') "Translate example" FROM DUAL；
-------------
我是Chin人,我爱Chin
一个汉字 = 2个字节
示例四：下面的示例证明，如果from_string的字符数大于to_string，那么多出的字符会被移除，也就是ina三个字符会从char参数中移除，当然区分大小写啦。
SELECT TRANSLATE('I am Chinese, I love China', 'China', '中国') "Translate example" FROM DUAL;
---------------
I m 中国ese, I love 中国
示例五：以下示例证明，如果第二个参数为空字符串，整个返回null。
SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'') "License"
FROM DUAL
示例六：在银行转帐时经常看见账户人只显示姓名的最后一个字，其余的用星号代替，我就用translate来做个类似的东西吧。
SELECT TRANSLATE('韩小明',substr('韩小明',1,length('韩小明') - 2), rpad('*',length('韩小明'),'*')) "name"
FROM DUAL
---------------
****明
但是名字是叠字的不行，如韩梅梅等。会返回******
可以使用replace函数
SELECT replace('韩梅梅',substr('韩梅梅',1,length('韩梅梅') - 2), rpad('*',length('韩梅梅'),'*')) "name"
FROM DUAL
---------------
****梅
REPLACE ( char, search_string [, replace_string])
如果没有指定replace_string 变量的值，那么当发现search_string 变量的值时，就将其删除。
select replace('0123456789','0','a') from dual;--a123456789
select replace('0123456789','0','') from dual;--123456789
select replace('0123456789','0') from dual;--123456789
补充：
rpad(string,padded_length,[pad_string])
从右边对字符串使用指定的字符进行填充
string 表示：被填充的字符串
padded_length 表示：字符的长度，是返回的字符串的数量，如果这个数量比原字符串的长度要短，rpad函数将会把字符串截取成从左到右的n个字符;
pad_string 是个可选参数，这个字符串是要粘贴到string的右边，如果这个参数未写，lpad函数将会在string的右边粘贴空格。
例如：
rpad('tech', 7); 将返回'tech '
rpad('tech', 2); 将返回'te'
rpad('tech', 8, '0'); 将返回'tech0000'
rpad('tech on the net', 15, 'z'); 将返回 'tech on the net'
rpad('tech on the net', 16, 'z'); 将返回 'tech on the netz'
2、截取
Instr()和substr()
instr函数对某个字符串进行判断，判断其是否含有指定的字符。返回字符的位置。
instr('源字符串' ,'目标字符串' ,'开始位置','第几次出现')
从一个字符串中查找指定子串的位置。例如：
SQL> select instr('yuechaotianyuechao','ao') position from dual;
POSITION
----------
6
从第7个字符开始搜索
SQL> select instr('yuechaotianyuechao','ao', 7) position from dual;
POSITION
----------
17
从第1个字符开始，搜索第2次出现子串的位置
SQL> select instr('yuechaotianyuechao','ao', 1, 2) position from dual;
POSITION
----------
17
substr函数的用法，取得字符串中指定起始位置和长度的字符串  ，默认是从起始位置到结束的子串。
substr('目标字符串',开始位置,长度)
substr('This is a test', 6, 2) would return 'is'
截取点号之前的字符串
SUBSTR ('C3411.907w15', 0, INSTR ('C3411.907w15, '.', 1, 1) - 1)
截取点号之后的字符串
SUBSTR ('C3411.907w15', INSTR ('C3411.907w15', '.', 1, 1)+1)
TRUNC
TRUNC()函数处理number型数字，只截取，不进行四舍五入
语法格式：TRUNC(number[,decimals])
其中： number 待做截取处理的数值；decimals 指明需保留小数点后面的位数，可选项，忽略它则截去所有的小数部分。
示例：
select trunc(123.567,2) from dual;--123.56,将小数点右边指定位数后面的截去;
select trunc(123.567,-2) from dual;--100,第二个参数可以为负数，表示将小数点左边指定位数后面的部分截去，即均以0记;
select trunc(123.567) from dual;--123,默认截去小数点后面的部分;
使用正则
REGEXP_LIKE：(匹配) 比较一个字符串是否与正则表达式匹配
(srcstr, pattern [, match_option])
REGEXP_INSTR：(包含)在字符串中查找正则表达式，并且返回匹配的位置
(srcstr, pattern [, position [, occurrence [, return_option [, match_option]]]])
REGEXP_SUBSTR：(提取) 返回与正则表达式匹配的子字符串
(srcstr, pattern [, position [, occurrence [, match_option]]])
REGEXP_REPLACE：(替换)搜索并且替换匹配的正则表达式
(srcstr, pattern [, replacestr [, position [, occurrence [, match_option]]]])
如：从字符串中提取数值
select regexp_replace('ORACLE_3035.2','[^0-9\.]','') from dual;  --> 返回：3035.2
[^0-9\.]'即除了0-9与小数点之外，其余被替换为空。
srcstr:        被查找的字符数据。
pattern:       正则表达式。
occurrence:    出现的次数。默认为1。
position:      开始位置
return_option: 默认值为0，返回该模式的起始位置；值为1则返回符合匹配条件的下一个字符的起始位置。
replacestr:    用来替换匹配模式的字符串。
match_option:  匹配方式选项。缺省为c。
i：大小写不敏感；
c：大小写敏感；(默认)
n：不匹配换行符号；
m：多行模式；
x：扩展模式，忽略正则表达式中的空白字符。
###########################################################################
边界符集合
^   每一行的开头，单行模式下等价于字符串的开头
$   每一行的结尾，单行模式下等价于字符串的结尾
在单行模式下，^和\A等价，$和\Z等价。
##########################################################################
重复次数集合
*   匹配零次或多次--尽可能多的次数
?   零次或一次
+   一次或多次
{m} {m}?正好m次，贪婪与非贪婪一样的
{m,}    {m,}?至少m次
{m, n}  {m, n}?最少m最多n次
在上述字符后加？，如：*?，即为非贪婪模式。
贪婪模式会获取尽可能多的字符，而非贪婪模式会获取尽可能少的字符
如最短匹配：
>>> import re
>>> str = 'this is first labelthe second label'
>>> print re.findall(r'(.*?)', str) # 最短匹配
['this is first label', 'the second label']
>>> print re.findall(r'(.*)', str)
['this is first labelthe second label']
###############################################################
组合操作符
[…]     方括号内任意字符或字符集合中的一个。在其中所有的操作符号都被视为普通符号，但以下除外：
★范围操作符：- 但当"-"位于字符列表的第一个或最后一个字符时，或位于范围的结尾时，会被当作"-"本身来看待。
当右方括号"]"出现在列表的第一个位置时，它会被当作"]"本身来处理。
[^…]    方括号内^为第一个字符时，表示与其后所有字符都不匹配的字符
(…)     圆括号，将复杂表达式当作单一表达式来处理
..|..    或
abc      和。直接将字符连在一起写
优先级比较：圆括号>重复次数操作符>和>或。
例如，(f|ht)tps?: 表示
ftp:
ftps:
http:
https:
######################################################################
匹配操作符
\n      即后向引用。n为1~9，标识由圆括号里取得的匹配字符串。方向是从左到右。
转义操作符
\       将其后紧跟着的操作字符当作普通字符看待。
例如 abc*def 可以匹配 abdef或abcccdef等，但无法匹配abc*def，后者需要abc\*def才能匹配
##################################################
一条正则表达式匹配函数示例解析：
SELECT * FORM tKHXX where  REGEXP_LIKE(SJHM,  '^[1]{1}[35]{1}[[:digit:]]{9}$' )
这个语句是从表中查手机号。
^ 表示开始
$ 表示结束
[]内部为匹配范围
{}里的内容表时个数
手机号码的特点是以 1开头接着是3或5再加9位的数字 所以这么理解
1开头 表达式为 ^[1]{1} 意为 开始1位里包含1
3或5 表达式为 [35]{1}，即第二位包含有3或5的
9位数字结束 为: [[:digit:]]{9}$ 这里[:digit:]为特殊写法，代表为数字，再加个结束符$。即最后9位是数字。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。