sql 换行符处理

 

There are times when you need to insert some special characters such as “newline”,”tab”
etc as values into a field. Usually every one tries with ‘\n’ or ‘\t’, in vain.
we can do this by using the ASCII value of the character. The steps are very simple.

if you want to insert a special character find the ASCII value of that character like ‘ 9 for tab’, ‘ 12 for new line’ etc.
you can find the lists of characters with its ASCII values at
http://awesomesql.wordpress.com/2009/08/10/ascii-character-set-table.

Once this is done, concatenate your string with the character of that particular ASCII value as
‘my string and’+ char(12)+’someting’

this will be

my string and

something

an examle is as shown below

INSERT INTO TABLES( FIELDS)

下面这句非常关键
SELECT ‘hello’+char(9)+’:'+char(12)+’this is a new line’

this will be stored in the table as
-------------------------

    hello    :

    this is a new line

--------------------------

in the above example I inserted a tab after hello and a new line after ‘:’

you can similarly use
下面是特殊字符所在的accii码

new line = char(12)

space = char(32)

horizontal tab = char(9)

carriage return = char(15)

vertical tab = char(13)

end of text = char(3)

sqlite 换行符号的处理
插入时： 'A' || X'0A' || 'B' 字符串A,B之间用 换行符连接起来

java 取出时 已经自动变成\n, 可以自动换行了。
当然也可以接去掉， 看个人需要
select replace(os_version, x'0a','\n') from tb_scan_host，　可以转换

 

附：editplus 替代特殊字符

 

There are times when you need to insert some special characters such as “newline”,”tab”
etc as values into a field. Usually every one tries with ‘\n’ or ‘\t’, in vain.
we can do this by using the ASCII value of the character. The steps are very simple.

if you want to insert a special character find the ASCII value of that character like ‘ 9 for tab’, ‘ 12 for new line’ etc.
you can find the lists of characters with its ASCII values at
http://awesomesql.wordpress.com/2009/08/10/ascii-character-set-table.

Once this is done, concatenate your string with the character of that particular ASCII value as
‘my string and’+ char(12)+’someting’

this will be

my string and

something

an examle is as shown below

INSERT INTO TABLES( FIELDS)

下面这句非常关键
SELECT ‘hello’+char(9)+’:'+char(12)+’this is a new line’

this will be stored in the table as
-------------------------

    hello    :

    this is a new line

--------------------------

in the above example I inserted a tab after hello and a new line after ‘:’

you can similarly use
下面是特殊字符所在的accii码

new line = char(12)

space = char(32)

horizontal tab = char(9)

carriage return = char(15)

vertical tab = char(13)

end of text = char(3)

sqlite 换行符号的处理
插入时： 'A' || X'0A' || 'B' 字符串A,B之间用 换行符连接起来

java 取出时 已经自动变成\n, 可以自动换行了。
当然也可以接去掉， 看个人需要
select replace(os_version, x'0a','\n') from tb_scan_host，　可以转换

附：editplus 替代特殊字符

3. 替代特殊字符 \r\n \r\n\r\n
    3.1 清除\r\n\r\n
    查找: (.)+(\\r\\n\\r\\n)(.)*
    替换: \1' || X'0A' || X'0A' || '\3

 

    3.2 清除\r\n
    查找: ([^\\n])+(\\r\\n)([^\\r])*
    替换: \1' || X'0A' || '\3