CODEWITHSUNDEEP
sql-serverstringreplace
user6428726
user6428726

Reputation:

Replace First Occurrence of Word in SqlServer

How to remove first occurrence of a word in SqlServer

SELECT REPLACE('SELECT SELECT SELECT','SELECT','HELLO')

Required output is 

HELLO SELECT SELECT

    SELECT 'FLAGY FLAGY' = RIGHT('FLAGY FLAGY', Len('FLAGY FLAGY')-5)

Upvotes: 2

Views: 3157

Answers (4)

Myo Myint Aung
Myo Myint Aung

Reputation: 147

You can try this 

DECLARE @string VARCHAR(20)
    SET @string = 'SELECT SELECT SELECT'
    SELECT 'HELLO ' + LEFT(@string, LEN(@string) - CHARINDEX(' ',@string))

Upvotes: 0

David Rushton
David Rushton

Reputation: 5030

You can use stuff to overwrite part of a string with another string. CharIndex tells you where the first occurance begins.

DECLARE @Txt NVARCHAR(255) = 'SELECT SELECT SELECT'

SELECT  
    ISNULL(STUFF(@Txt, CHARINDEX('SELECT', @Txt), LEN('SELECT'), 'HELLO'), @Txt)
;

Upvotes: 0

Andrey Nadezhdin
Andrey Nadezhdin

Reputation: 126

Here is an example:

DECLARE @string VARCHAR(100) = 'SELECT SELECT SELECT'
DECLARE @word VARCHAR(100) = 'SELECT'
DECLARE @newWord VARCHAR(100) = 'WORD'
SELECT @string, stuff(@string, charindex(@word, @string), len(@word), @newWord)

Upvotes: 0

wero
wero

Reputation: 33000

Use CHARINDEX to find the first occurrence, STUFF to replace, and COALESCE in case the string is not found:

DECLARE @input varchar(1000)
SELECT @input='SELECT SELECT SELECT'

SELECT COALESCE(STUFF(@input, CHARINDEX('SELECT', @input), 6, 'HELLO'), @input)

Upvotes: 4

Related Questions