sqlite(sql)替換特殊字元(換行,tab鍵等)

NO IMAGE

換tab 

sql語句:

UPDATE table_name SET field_name=REPLACE(field_name,char(09),”)

但是 sqlite會報錯

在sqlite中 換tab 為x’09’所以sql為:

 update  question_select set title =replace(title, x’09’,”)

這寫其實都是ASCLL碼,無論是char(09) 或x‘09’

如果想替換回車字元改成13

UPDATE table_name SET field_name=REPLACE(field_name,char(13),”)

UPDATE table_name SET field_name=REPLACE(field_name,x’13’,‘’)

以此類推! 

如果想要替換單條資料可加上where 條件判斷

ASCII碼錶 0-127

BinDecHex縮寫/字元解釋
00000000000NUL(null)空字元
00000001101SOH(start of headling)標題開始
00000010202STX (start of text)正文開始
00000011303ETX (end of text)正文結束
00000100404EOT (end of transmission)傳輸結束
00000101505ENQ (enquiry)請求
00000110606ACK (acknowledge)收到通知
00000111707BEL (bell)響鈴
00001000808BS (backspace)退格
00001001909HT (horizontal tab)水平製表符
00001010100ALF (NL line feed, new line)換行鍵
00001011110BVT (vertical tab)垂直製表符
00001100120CFF (NP form feed, new page)換頁鍵
00001101130DCR (carriage return)回車鍵
00001110140ESO (shift out)不用切換
00001111150FSI (shift in)啟用切換
000100001610DLE (data link escape)資料鏈路轉義
000100011711DC1 (device control 1)裝置控制1
000100101812DC2 (device control 2)裝置控制2
000100111913DC3 (device control 3)裝置控制3
000101002014DC4 (device control 4)裝置控制4
000101012115NAK (negative acknowledge)拒絕接收
000101102216SYN (synchronous idle)同步空閒
000101112317ETB (end of trans. block)傳輸塊結束
000110002418CAN (cancel)取消
000110012519EM (end of medium)介質中斷
00011010261ASUB (substitute)替補
00011011271BESC (escape)溢位
00011100281CFS (file separator)檔案分割符
00011101291DGS (group separator)分組符
00011110301ERS (record separator)記錄分離符
00011111311FUS (unit separator)單元分隔符
001000003220(space)空格
001000013321! 
001000103422 
001000113523# 
001001003624$ 
001001013725% 
001001103826& 
001001113927 
001010004028( 
001010014129) 
00101010422A* 
00101011432B 
00101100442C, 
00101101452D 
00101110462E. 
00101111472F/ 
0011000048300 
0011000149311 
0011001050322 
0011001151333 
0011010052344 
0011010153355 
0011011054366 
0011011155377 
0011100056388 
0011100157399 
00111010583A: 
00111011593B; 
00111100603C< 
00111101613D= 
00111110623E> 
00111111633F? 
010000006440@ 
010000016541A 
010000106642B 
010000116743C 
010001006844D 
010001016945E 
010001107046F 
010001117147G 
010010007248H 
010010017349I 
01001010744AJ 
01001011754BK 
01001100764CL 
01001101774DM 
01001110784EN 
01001111794FO 
010100008050P 
010100018151Q 
010100108252R 
010100118353S 
010101008454T 
010101018555U 
010101108656V 
010101118757W 
010110008858X 
010110018959Y 
01011010905AZ 
01011011915B[ 
01011100925C\ 
01011101935D] 
01011110945E^ 
01011111955F_ 
011000009660` 
011000019761a 
011000109862b 
011000119963c 
0110010010064d 
0110010110165e 
0110011010266f 
0110011110367g 
0110100010468h 
0110100110569i 
011010101066Aj 
011010111076Bk 
011011001086Cl 
011011011096Dm 
011011101106En 
011011111116Fo 
0111000011270p 
0111000111371q 
0111001011472r 
0111001111573s 
0111010011674t 
0111010111775u 
0111011011876v 
0111011111977w 
0111100012078x 
0111100112179y 
011110101227Az 
011110111237B{ 
011111001247C| 
011111011257D} 
011111101267E~ 
011111111277FDEL (delete)刪除

參考:

http://www.asciima.com/

http://www.cnblogs.com/kevinGao/archive/2012/05/29/2524538.html

http://www.qcfupo.com/Html/?1418.html