SQL - Replace characters in a SQL string

Question

I would like to know how to replace all characters between 2 dashes (-) and replace the dashes as well. It should be in a single select statement, not a loop. The text between the dashes can be variable length and any character. The characters before and after the dashes can also be variable length.

        141911001-200-20     = 14191100120  // Replaced -200-
        141911001-10-50      = 14191100150  // Replaced -10-
        141911001-50-10      = 14191100110  // Replaced -50-
        141911001-aaaa-85    = 14191100185  // Replaced --aaaa--
        11111111111-bbbbb-4444 = 111111111114444 //Replace-bbbbb-

Tag your question with the database you are using.

Gordon Linoff
– Gordon Linoff

2021-07-22 15:31:48 +00:00
Commented Jul 22, 2021 at 15:31 — Gordon Linoff
– Gordon Linoff, Commented Jul 22, 2021 at 15:31

Rafael Souza · Accepted Answer · 2021-07-22 15:54:03Z

1

You can do it by using CHARINDEX, SUBSTRING and LEN

CREATE TABLE #TEMP
(
  test_value VARCHAR(20)
)

INSERT INTO #TEMP
VALUES ('141911001-200-20')
,('141911001-10-50')
, ('141911001-50-10')
,('141911001-aaaa-85')

SELECT SUBSTRING(test_value, CHARINDEX('-', test_value), LEN(test_value)) AS subFirstDash -- Substring starts on the first dash (-) index
 , CHARINDEX('-', SUBSTRING(test_value, CHARINDEX('-', test_value) + 1, LEN(test_value))) AS lastDashIndex -- Last dash(-) index from the previous substring
 , SUBSTRING(SUBSTRING(test_value, CHARINDEX('-', test_value) + 1, LEN(test_value)), 0, CHARINDEX('-', SUBSTRING(test_value, CHARINDEX('-', test_value) + 1, LEN(test_value)))) AS btwDashes -- Value between dashes (-)
 , REPLACE(test_value, '-' + SUBSTRING(SUBSTRING(test_value, CHARINDEX('-', test_value) + 1, LEN(test_value)), 0, CHARINDEX('-', SUBSTRING(test_value, CHARINDEX('-', test_value) + 1, LEN(test_value)))) + '-', '') AS new_value -- Value between dashes (-) replaced 
 , test_value AS original_value
FROM #TEMP

DROP TABLE #TEMP

Check this fiddle

edited Jul 22, 2021 at 15:54

answered Jul 22, 2021 at 15:49

Rafael Souza

595 bronze badges

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

1 Comment

Stanton Roux Over a year ago

Brilliant exactly what i was looking for

Gordon Linoff · Accepted Answer · 2021-07-22 17:41:33Z

0

This answers the original version of the question.

In your sample data, the first and last elements are the same length, so you can use string operations. Most databases have left() and right() functions (otherwise use the substr() function).

So, if that is generally true, you can use:

left(col, 9) || right(col, 3)

edited Jul 22, 2021 at 17:41

answered Jul 22, 2021 at 15:33

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

Stanton Roux Over a year ago

Good thought but unfortunately first and last elements can be variable length aswell. I have updated question

Collectives™ on Stack Overflow

SQL - Replace characters in a SQL string

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related