replacing underscore with a tab

[Robert Citek-2]

How can I replace an underscore ("_") in a field with a tab?

This works but seems like suck a hack:

$ sqlite3 foobar.db 'select replace(id,"_","{tab}") from bar;' |
  sed -e 's/{tab}/\t/'

I was hoping for a char(9) or similar but couldn't find anything in the docs:

http://www.sqlite.org/lang_corefunc.html

Pointers to references greatly appreciated.

Regards,
- Robert
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Igor Tandetnik]

Robert Citek <[hidden email]>
wrote:

> How can I replace an underscore ("_") in a field with a tab?
>
> This works but seems like suck a hack:
>
> $ sqlite3 foobar.db 'select replace(id,"_","{tab}") from bar;' |
>  sed -e 's/{tab}/\t/'
>
> I was hoping for a char(9) or similar but couldn't find anything in
> the docs:
>
> http://www.sqlite.org/lang_corefunc.html

There's no special SQLite function because none is needed. SQLite will
quite happily accept a string literal containing a TAB character. The
trick is to enter one on the command line - and that's an issue with the
shell, not with SQLite.

Assuming you use bash shell, try this:

sqlite3 foobar.db $'select replace(id,\'_\',\'\t\') from bar;'

or

echo -e "select replace(id,'_','\t') from bar;" | sqlite3 foobar.db


But if you insist on doing it in SQL, this should work:

sqlite3 foobar.db "select replace(id,'_',cast(x'09' as text)) from bar;"


Igor Tandetnik



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Robert Citek-2]

On Tue, Sep 16, 2008 at 3:49 PM, Igor Tandetnik <[hidden email]> wrote:
> But if you insist on doing it in SQL, this should work:
>
> sqlite3 foobar.db "select replace(id,'_',cast(x'09' as text)) from bar;"

That worked:

$ sqlite3 foobar.db 'select replace(id,"_",cast(x"09" as text)) from bar;'

Although, using cast was non-obvious to me from reading the docs:

http://www.sqlite.org/lang_expr.html

Here's another version using bash syntax to insert the tab character:

$ sqlite3 foobar.db 'select replace(id,"_","'$'\t''") from bar;'

Thanks, Igor.

Regards,
- Robert
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users