CODEWITHSUNDEEP
sql
Stanton Roux
Stanton Roux

Reputation: 151

SQL - Replace characters in a SQL string

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-

Upvotes: 0

Views: 318

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

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)

Upvotes: 0

Rafael Souza
Rafael Souza

Reputation: 59

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

Upvotes: 1

Related Questions