-1

I have a table with multiple entries in the "Test1" column

These are for example:

123ABCsignal2342
23ABsignal234signal
ABBSDsignal2signal

I want to update the entries like this:

123ABC.signal2342  
23AB.signal234signal 
ABBSD.signal2signal

The position for inserting the dot (.) is always in a different place and is signed by the expression "signal". Even if "signal" occurs several times, the point should be inserted only once at the beginning.

Squirrel
  • 23,507
  • 4
  • 34
  • 32
tommy74
  • 3
  • 2
  • Please share what you have tried so far and what you are having trouble with. – Stu Jun 20 '22 at 09:41
  • 1
    use [charindex](https://learn.microsoft.com/en-us/sql/t-sql/functions/charindex-transact-sql?view=sql-server-ver16) to find the first occurrence and then use [stuff](https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16) the dot in. – Squirrel Jun 20 '22 at 09:43
  • 1
    SQL operates on rows, not substrings. What you ask is replacing one string with another, not INSERTing – Panagiotis Kanavos Jun 20 '22 at 09:43

1 Answers1

2
Declare @strs varchar(50) = '23ABsignal234signal'
SELECT STUFF(@strs
               , CHARINDEX('signal', @strs)
               , LEN('signal')
               , '.signal')

OR

As Suggested Squirrel

Declare @strs varchar(50) = '23ABsignal234signal'
SELECT STUFF(@strs, CHARINDEX('signal', @strs), 0, '.')
Roshan Nuvvula
  • 803
  • 1
  • 8
  • 28