SQL replace query

Maybe It is very simple for a person who are using database every day, but I need to write sql query. I've got fields (85500 items) which are in the same design for example '200 00 2334', '200 00 2555' (it's varchar data), it's a code of customers. I need to change all of this fields to for example '200-00 2334', '200-00 2555'. The first space in the field must be replaced into '-' the second space must stay without changes. My database is a PostgreSQL. I need only a query.

Thanks a lot for any help!

Answers


As chance would have it regexp_replace() will do the job with a trivial pattern-match since it will only match the first space.

SELECT regexp_replace('100 200 300', ' ', '-');
 regexp_replace 
----------------
 100-200 300

select code[1]||'-'||code[2]||' '||code[3] from
 (select regexp_split_to_array('200 00 2334', E'\\s+') as code) t

This keeps you more flexible if the first part of the code has different amount of charaters


Assuming it's always the 4th character, you can use overlay:

select overlay('200 00 12132' placing '-' from 4 for 1);
--------------------------------------------------------
OVERLAY
200-00 12132

Need Your Help

Encode/decode JSON keys?

java json parsing jackson gson

I want to send a minified version of my JSON by minifying the keys.

How to make an ODBC connection in Android?

java android sql-server odbc

I am wondering if there is a way to make an ODBC connection to a sql azure database through android. What I am trying to do is make an app that lists farmer info for a coffee company Ex(Name, Cuppi...