SQL中如何从字符串中取值。 sql怎么取字符串中的数字
SQL\u600e\u6837\u53d6\u51fa\u5b57\u7b26\u4e32\u4e2d\u7684\u6570\u5b57\uff1f\u6bd4\u5982\u4f60\u7684\u8868\u53eb\u4f5cTALBE1,\u5b57\u6bb5\u662fstr1,\u7c7b\u578b\u4e3anvarchar,\u90a3\u4e48\u627e\u51fa\u6709\u6e58\u653f\u53d1\u7684\u6700\u5927\u53f7\u52a01\u8fd9\u6837\u5199
select max(convert(int,substring(str1,patindex(N'%[1234567890]%',str1),len(str1)-patindex(N'%[1234567890]%',str1)+1-patindex(N'%[1234567890]%',reverse(str1))+1)))+1 from table1 where str1 like N'%\u6e58\u653f\u53d1%'
\u6d4b\u8bd5\u901a\u8fc7\uff0c\u8017\u65f6\u5f88\u957f\uff0c\u8bf7\u8ba4\u771f\u8bd5\u8bd5\u3002
\u8fd9\u662fsql server \u7684\u5199\u6cd5\u3002
\u5982\u679c\u662fORACAL\u6216\u5176\u5b83\u7684SQL\u8bed\u8a00\u60a8\u53ef\u4ee5\u5230\u8fd9\u91cc\u53c2\u8003\u4e00\u4e0b\u3002\u4f60\u4f1a\u53d1\u73b0\uff0c\u7528SQL SERVER\u6211\u7684\u65b9\u6cd5\u8981\u6bd4\u4ed6\u7684\u597d
http://book.csdn.net/bookfiles/530/10053017904.shtml
select
substring(\u6240\u67e5\u8be2\u5b57\u7b26\u4e32,patindex('%[^0-9][0-9]%',\u6240\u67e5\u8be2\u5b57\u7b26\u4e32)+1,patindex('%[0-9][^0-9]%',\u6240\u67e5\u8be2\u5b57\u7b26\u4e32)-patindex('%[^0-9][0-9]%',\u6240\u67e5\u8be2\u5b57\u7b26\u4e32))\u8fd9\u4e2a\u53ea\u80fd\u67e5\u8be2\u7b2c\u4e00\u6b21\u5728\u5b57\u7b26\u4e32\u51fa\u73b0\u7684\u6570\u5b57\u4e32,\u5e0c\u671b\u53ef\u5e2e\u5230\u4f60\u4f60
就是两个函数
substring("1231",1,2) ="12" 1起始位置 2截取长度
charindex('(','ab(cd')=3 获取在字段'ab(cd' 中‘(’字符在字符串中的索引位置
select substr(ce,instr(ce,'(')-1,instr(ce,')')) from ce
这个好像不可以把,因为你的一个值里面左边长度和右边长度都不确定,也没什么特点,咱们取出来?
绛旓細select regexp_substr('姘村簱鏂版潙332鍙','[0-9]+' ) from dual;--鐢ㄦ鍒欒〃杈惧紡鍙栧嚭鏁板瓧 select regexp_substr('姘村簱鏂版潙332鍙','[[:digit:]]+') from dual;
绛旓細name="寮犲洓,鏉庝笁,浜斾簲";sql="select * from [user] where [name] in (";string[] namearray=name.split(',');//"&name&")for(int i=0;i<namearray.length;i++){ sql+="''&namearray[i]&"'";} sql+=")";杩欐牱灏卞緱鍒颁綘鎯宠鐨SQL璇彞浜嗐
绛旓細sql涓锛屼娇鐢↙EFT鍑芥暟鍗冲彲鍙栧埌瀛楃涓鐨勫墠鍑犱綅銆侺EFT(c,number_of_char)鐢ㄤ簬杩斿洖鏌愪釜琚姹傜殑鏂囨湰鍩熺殑宸︿晶閮ㄥ垎锛屽叾涓璫浠h〃琚姹傜殑鏂囨湰鍩燂紝number_of_cha浠h〃闇瑕佸彇鍑虹殑瀛楃涓蹭綅鏁般傚鈥淟EFT(".",6)鈥濆嵆鍙彇寰楀瓧绗︿覆""銆
绛旓細select left('2绠+3閮',CHARINDEX('+','2绠+3閮')-2)+'*16+'+left(right('2绠+3閮',len('2绠+3閮')-CHARINDEX('+','2绠+3閮')),len(right('2绠+3閮',len('2绠+3閮')-CHARINDEX('+','2绠+3閮')))-1)declare @s nvarchar(100)set @s='123绠+8閮'select left(...
绛旓細棰樹富浣犲ソ,璇曡瘯:select regexp_substr(瀛楁鍚,"(?<=:).*(?=\\})") from 琛ㄥ悕;--- 甯屾湜鍙互甯埌棰樹富, 娆㈣繋杩介棶.
绛旓細sql 鎴彇瀛楃涓锛1銆丩OCATE(substr , str )锛氳繑鍥炲瓙涓 substr 鍦ㄥ瓧绗︿覆 str 涓涓娆″嚭鐜扮殑浣嶇疆锛屽鏋滃瓧绗ubstr鍦ㄥ瓧绗︿覆str涓笉瀛樺湪锛屽垯杩斿洖0锛2銆丳OSITION(substr IN str )锛氳繑鍥炲瓙涓 substr 鍦ㄥ瓧绗︿覆 str 涓涓娆″嚭鐜扮殑浣嶇疆锛屽鏋滃瓧绗ubstr鍦ㄥ瓧绗︿覆str涓笉瀛樺湪锛屼笌LOCATE鍑芥暟浣滅敤鐩稿悓锛3銆...
绛旓細vba绀轰緥浠g爜濡備笅锛歱rivate sub command1_click()dim x,y as stringx=text1 '鍙栨枃鏈涓殑瀛楃涓for i=1 to len锛坸锛 鈥樺惊鐜彇x鐨勬瘡涓涓瓧绗 if asc锛坢id锛坸锛宨锛1锛夛級between 48 and 57 鈥樺垽鏂浣嶅瓧绗︽槸鍚﹀湪0鍒9涔嬮棿锛岄氳繃asc鐮佸垽鏂 y=y+mid锛坸锛宨锛1锛夆欏皢璇ヤ綅瀛楃杩涜杩炴帴 end ...
绛旓細濡傛灉鏄涓浣嶏紝鍙互鐩存帴鐢╨eft()鍑芥暟 select left(book_id,1) from 琛ㄨ繖鏍峰氨鍙互浜 濡傛灉鏄埅鍙栫涓浣嶅悗鐨勫墠浜屼綅锛岃鐢╯ubstring()鍑芥暟 select substring(book_id,2,2)from 琛ㄨ繖鏍峰氨鍙互浜 鏈夐棶棰樿缁х画杩介棶
绛旓細SQL鎴彇瀛楃涓插嚱鏁帮細A.鎴彇浠庡瓧绗︿覆宸﹁竟寮濮婲涓瓧绗 浠ヤ笅鏄唬鐮佺墖娈碉細Declare @S1 varchar(100)Select @S1='http://www.xrss.cn'Select Left(@S1,4)--- 鏄剧ず缁撴灉: http B.鎴彇浠庡瓧绗︿覆鍙宠竟寮濮婲涓瓧绗(渚嬪鍙栧瓧绗ww.163.com)浠ヤ笅鏄唬鐮佺墖娈碉細Declare @S1 varchar(100)Select @S1='http:...
绛旓細declare @s varchar(100),@f varchar(10)select @s='婧瀛楃涓',@f='绛夋煡瀛楃涓'select (len(@s)-len(replace(@s,@f,'')))/len(@f)