sql: remove special characters

[former_member1338072]

Hello!

maybe someone has a hint for me how to remove special characters such as tab/new line characters from columns?

Thanks in advance!

Tiberius

[lbreddemann]

Hi there,

how about [REPLACE|http://maxdb.sap.com/doc/7_7/44/d9eed6b7f74427e10000000a155369/frameset.htm] function?

regards,

Lars

[former_member1338072]

hm, yes, but I can't figure out the syntax how to find character values starting with e.g. a tab.

select col1 from table1 where col1 like x'9%' doesn't work. I think it only works for byte columns.

If I could find the values I could also replace them...

[lbreddemann]

Hmm... actually it's straight forward:

drop table mytexts
//
create table mytexts (comment varchar(50))
//
insert into mytexts values ('This is a standard text!')
//
insert into mytexts values ('There comes a TAB	<--')
//
insert into mytexts values ('This is called a newline
<--<')

//
select * from mytexts

| COMMENT                                            |
| -------------------------------------------------- |
| This is a standard text!                           |
| There comes a TAB     <--                              |
| This is called a newline
<--<                     |

3 rows selected (1375 usec)




//
sqlcli db770=> select replace (replace(comment, '       '), '
> ') as cleaned_comment from mytexts
> ;
| CLEANED_COMMENT                                    |
| -------------------------------------------------- |
| Thisisastandardtext!                               |
| TherecomesaTAB<--                                  |
| Thisiscalledanewline<--<                           |

3 rows selected (1662 usec)

Seems to work...

regards,

Lars

[former_member1338072]

thank You for your help, but my problem still exists, because for example I have to replace new line by '<br>' or remove a leading tab in a column, so I must not remove all special characters from a column including all white spaces therein.

regards

Tiberius

[lbreddemann]

Hi there - hmm... seems to be a forum formatting issue.

Try the example on your own machine and check if the results are OK for your purpose.

regards,

Lars

[former_member1338072]

oh, I mean replace new line by

'<br>'

...

[lbreddemann]

So?

REPLACE has a third optional parameter that allows you to specifiy what should be put into the string instead of what you replace.

regards,

Lars

[former_member1338072]

sorry for the confusion, it seems to work in sqlcli, that I never use for my sql stuff before, in Database Studio it won't work.

Thanks!

[lbreddemann]

it actually does work in DB Studio - but you've to double click on the result grid to get the unfiltered value.

The listgrid used in DB Studio does some filtering automatically...

regards,

Lars