Replace First Occurrence of Substring in a String in SQL

Replace first occurrence of substring in a string in SQL

You can use a combination of STUFF and CHARINDEX to achieve what you want:

SELECT STUFF(col, CHARINDEX('substring', col), LEN('substring'), 'replacement')
FROM #temp

CHARINDEX('substring', col) will return the index of the first occurrence of 'substring' in the column. STUFF then replaces this occurrence with 'replacement'.

Replace first occurrence of '.' in sql String

Use STUFF function

Find the first occurance of . using CHARINDEX and remove it using STUFF

SELECT STUFF(valuesT, CHARINDEX('.', valuesT), 1, '')
FROM TableT

SQL: Replacing only the first of a certain character

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        |

Replace ONLY the first occurrence of a string in SQL

Here's the correct query. Just convert to an update command!
Also, I've used a generic approach to solve the problem.

    SELECT 
      phone_number
    , STUFF(phone_number, CHARINDEX('00', phone_number), LEN('00'), '+') as ExpectedResult
    FROM dbo.phone 
    WHERE phone_number_numeric LIKE '00%'

The REPLACE function looks at the entire string. Use STUFF function to narrow the scope down to the desired limit.

SQL: search/replace but only the first time a value appears in record

This should actually be what you want in MySQL:

UPDATE wp_post
SET post_content = CONCAT(REPLACE(LEFT(post_content, INSTR(post_content, 'A')), 'A', 'B'), SUBSTRING(post_content, INSTR(post_content, 'A') + 1));

It's slightly more complicated than my earlier answer - You need to find the first instance of the 'A' (using the INSTR function), then use LEFT in combination with REPLACE to replace just that instance, than use SUBSTRING and INSTR to find that same 'A' you're replacing and CONCAT it with the previous string.

See my test below:

SET @string = 'this is A string with A replace and An Answer';
SELECT @string as actual_string
, CONCAT(REPLACE(LEFT(@string, INSTR(@string, 'A')), 'A', 'B'), SUBSTRING(@string, INSTR(@string, 'A') + 1)) as new_string;

Produces:

actual_string                                  new_string
---------------------------------------------  ---------------------------------------------
this is A string with A replace and An Answer  this is B string with A replace and An Answer

Spark - Replace first occurrence in a string

Replacing the first occurrence isn't something I can see supported out of the box by Spark, but it is possible by combining a few functions:

Spark >= 3.0.0

import org.apache.spark.sql.functions.{array_join, col, split}

val test0 = Seq("abcdefgbchijkl").toDF("col0") // replaced `var` with `val`

val stringToReplace = "bc"
val replacement = "**BC**"

test0
  // create a temporary column, splitting the string by the first occurrence of `bc`
  .withColumn("temp", split(col("col0"), stringToReplace, 2))
  // recombine the strings before and after `bc` with the desired replacement
  .withColumn("col0", array_join(col("temp"), replacement))
  // we no longer need this `temp` column
  .drop(col("temp"))
  .show(false)

gives:

+------------------+
|col0              |
+------------------+
|a**BC**defgbchijkl|
+------------------+

---

For (spark) SQL:

-- recombine the strings before and after `bc` with the desired replacement
SELECT tempr[0] || "**BC**" || tempr[1] AS col0
FROM (
  -- create a temporary column, splitting the string by the first occurrence of `bc`
  SELECT split(col0, "bc", 2) AS tempr
  FROM (
    SELECT 'abcdefgbchijkl' AS col0
  )
)

---

Spark < 3.0.0 (pre 2020, tested using Spark 2.4.5)

val test0 = Seq("abcdefgbchijkl").toDF("col0")

val stringToReplace = "bc"
val replacement = "**BC**"

val splitFirst = udf { (s: String) => s.split(stringToReplace, 2) }

spark.udf.register("splitFirst", splitFirst) // if you're using Spark SQL

test0
  // create a temporary column, splitting the string by the first occurrence of `bc`
  .withColumn("temp", splitFirst(col("col0")))
  // recombine the strings before and after `bc` with the desired replacement
  .withColumn("col0", array_join(col("temp"), replacement))
  // we no longer need this `temp` column
  .drop(col("temp"))
  .show(false)

gives:

+------------------+
|col0              |
+------------------+
|a**BC**defgbchijkl|
+------------------+

For (spark) SQL:

-- recombine the strings before and after `bc` with the desired replacement
SELECT tempr[0] || "**BC**" || tempr[1] AS col0
FROM (
  -- create a temporary column, splitting the string by the first occurrence of `bc`
  SELECT splitFirst(col0) AS tempr -- `splitFirst` was registered above
  FROM (
    SELECT 'abcdefgbchijkl' AS col0
  )
)

Replacing first occurrence of character with one character and the second with another character in SQL

First an example of how the syntax works

DECLARE @a varchar(max) = 'fgh"abc"sdf'

SELECT 
  stuff(stuff(@a, charindex('"', @a),1, '«'), 
    charindex('"', @a, charindex('"', @a) + 1), 1, '»')

Result:

fgh«abc»sdf 

This is the query needed where col is the name of your column:

SELECT 
  stuff(stuff(col, charindex('"', col),1, '«'), 
    charindex('"', col, charindex('"', col) + 1), 1, '»')
FROM yourtable

---

Related Topics