SQL replace query

Question

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!

Richard Huxton · Accepted Answer · 2012-06-15 09:55:50Z

2

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

answered Jun 15, 2012 at 9:55

Richard Huxton

23.6k5 gold badges43 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BKl Over a year ago

that's what I want. I've used the query: update konta_wirtualne set kw_kod_kontrahenta = regexp_replace(kw_kod_kontrahenta, ' ', '-')

Thomas · Accepted Answer · 2012-06-15 09:59:01Z

1

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

answered Jun 15, 2012 at 9:59

Thomas

1,0669 silver badges19 bronze badges

Comments

aF. · Accepted Answer · 2012-06-15 10:03:55Z

1

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

edited Jun 15, 2012 at 10:03

answered Jun 15, 2012 at 9:57

aF.

67k45 gold badges141 silver badges201 bronze badges

Collectives™ on Stack Overflow

SQL replace query

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related