0

I'm trying to replace the first of a certain character in my SQL query. In my case I'm trying to replace the first " - " by a " ; ", but not the second

This is a simplified version of the query i've tried, but it replaces both "-"

SELECT REPLACE (xColumn, '-',';')FROM xTable

Example 1:

Original data: COMP-LAP-0001

My result: COMP;LAP;0001

Expected result: COMP;LAP-0001

Example 2:

Original data: COMP-0001

My result: COMP;0001

Expected result: COMP;0001

Christopher Boisvert
  • 15
  • 7
  • 3
    [Replace first occurrence of substring in a string in SQL](https://stackoverflow.com/questions/38911588/replace-first-occurrence-of-substring-in-a-string-in-sql#38911646) – Lukasz Szozda Sep 17 '19 at 19:45
  • Thank You @LukaszSzozda but this will cause the result to be trancated? my result can't be cut off after the " - " – Christopher Boisvert Sep 17 '19 at 19:50

1 Answers1

2

You can use CHARINDEX() to locate the first occurence of the given character in the string, and then replace it with STUFF():

SELECT STUFF(xColumn, CHARINDEX('-', xColumn), 1, ';') FROM xTable

Demo on DB Fiddle:

WITH xTable AS (
    SELECT 'COMP-LAP-0001' xColumn
    UNION ALL SELECT 'COMP-0001'
)
SELECT STUFF(xColumn, CHARINDEX('-', xColumn), 1, ';') FROM xTable

| (No column name) |
| :--------------- |
| COMP;LAP-0001    |
| COMP;0001        |
GMB
  • 216,147
  • 25
  • 84
  • 135
  • 2
    This will truncate the output after replacing the first - with ;. Instead of LEN(xColumn) it should be 1. – Sean Lange Sep 17 '19 at 19:45
  • I don't want the result to be trancated, i just need to replace the first " - " with a " ; " without losing any other characters – Christopher Boisvert Sep 17 '19 at 19:48
  • @SeanLange: yes you are right, I fixed the query (and added a DB Fiddle). Thank you. – GMB Sep 17 '19 at 19:48
  • 1
    This now works exactly as the OP is looking for. +1 – Sean Lange Sep 17 '19 at 19:49
  • Thanks @GMB but I have 4000+ data in that column they don't all start and end with the same characters. Example of the data i have ORDI-09912, ABC-CBA-3234, ACER192. Will this query give me the expected results? – Christopher Boisvert Sep 17 '19 at 20:01
  • 1
    You need to use the query in the select statement and replace your table and columns with your proper names. This is an example using a cte as way to have a table to select from. – Sean Lange Sep 17 '19 at 20:08
  • @ChristopherBoisvert: this should work as expected. You can play around and test the query in the db fiddle link that I provided in the answer. Let me know if something is not working as intended. – GMB Sep 17 '19 at 20:09
  • Sorry I didn't see the edited query, this new query works! Just needed to add a ISNULL for the results that didn't include " - ". Thanks! – Christopher Boisvert Sep 17 '19 at 20:21
  • And thanks to @SeanLange for following up on the thread! – GMB Sep 17 '19 at 20:23