31

I have to fetch data from a @temp table which has something like "or ccc or bbb or aaa" I want to replace the first occurrence into space to get something like this " ccc or bbb or aaa". I am trying stuff and replace but they don't seem to get me the desired result

What I have tried:

DECLARE @stringhere as varchar(500)

DECLARE @stringtofind as varchar(500)

set @stringhere='OR contains or cccc or  '

set @stringtofind='or'
select STUFF('OR contains or cccc or  ',PATINDEX('or', 'OR contains or cccc or  '),0 ,' ')
Improve this question
3
  • Are you trying to make a where clause for a dynamic query? Commented Aug 12, 2016 at 7:04
  • i am trying to get a final value after removing the first occurrence of a string.. Commented Aug 12, 2016 at 7:21
  • Please read this community discussion about urgent begging, and be informed that this is not an appropriate way to address volunteers. Thanks! Commented Aug 12, 2016 at 8:25

6 Answers 6

Reset to default
61

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'.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thank you so much sir, worked absolutely fine... searched hours for this got no good enough result. ... Thank you
Glad to help you ... and you asked a pretty good question :-)
Bit late (but surprised this wasn't noted before), but this only seems to work if there is an occurrence of 'substring'. If there isn't, null is returned. So a CASE expression to catch that is advisable.
@HoneyBadger Nice catch. Or, we could just wrap the entire call to STUFF inside COALESCE, and replace null values with the original column.
This has an issue with collations though: The matching substring may have a different length than the search string ('hä' is in 'haegar' in German_PhoneBook_100_CI_AS_SC_UTF8).
3

it seems you miss 2% preceding and trailing to the target string

please try:

select STUFF(@stringhere, PATINDEX('%' + @stringtofind + '%', @stringhere), LEN(@stringtofind), ' ')
Improve this answer

2 Comments

@user3331798 : please try
@user3331798 : Thanks, but why there are 2 single quotes in '''OR contains or cccc or '?
2

You can do a CHARINDEX or a PATINDEX, as shown above, but I would also recommend adding a COALESCE, in case your @stringtoFind it not included in your @stringhere.

SELECT COALESCE(STUFF(@stringhere, PATINDEX('%' + @stringtofind + '%', @stringhere), LEN(@stringtofind), ' '), @stringhere)
Improve this answer

Comments

1

I had the same problem and made the same response as Tim Biegeleisen, but in a function:

CREATE FUNCTION DBO.FN_REPLACE_FIRST(@X NVARCHAR(MAX), @F NVARCHAR(MAX), @R NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) AS BEGIN
RETURN STUFF(@X, CHARINDEX(@F, @X), LEN(@F), @R)
END

So I just call the function instead:

SELECT DBO.FN_REPLACE_FIRST('Text example', 'ex', 'eexx') --> Returns 'Teexxt example'

The explanation is the same

Improve this answer

Comments

1

To modify only the first letters add a "WHEN CHARINDEX(@FindText, @Text) = 1" If not, you risk replacing another letter in your sentence if it is not present.

DECLARE 
    @Text varchar(MAX),
    @TextReplaceBy varchar(20),
    @FindText varchar(20);

SET @Text = 'Text to replace';
SET @FindText = 't';
SET @TextReplaceBy = '';
SELECT
CASE 
    WHEN CHARINDEX(@FindText, @Text) = 1
      THEN STUFF(@Text, CHARINDEX(@FindText, @Text), LEN(@FindText), @TextReplaceBy) 
    ELSE @Text
END
Improve this answer

1 Comment

Good point, but simpler to use NULLIF to check if the match doesn't exist. for example SELECT STUFF(@Text, NULLIF(CHARINDEX(@FindText, @Text), 0), LEN(@FindText), @TextReplaceBy), @Text)
-1

https://www.dotnetiseasy.com/Artical/SQL/21/Replace%20string%20as%20camelCase%20in%20MSSQL

DROP TABLE IF EXISTS #YourTableName;
CREATE TABLE #YourTableName (yourColumn VARCHAR(20));
INSERT INTO #YourTableName VALUES ('abcd_abcd_abcd'), ('dfredsdfsf');

;WITH CTE AS 
(
    SELECT 
        ISNULL(STUFF(yourColumn, CHARINDEX('_', yourColumn), 2, UPPER(SUBSTRING(yourColumn, CHARINDEX('_', yourColumn) + 1, 1))), yourColumn) AS yourColumn,
        CHARINDEX('_', yourColumn) AS b,
        0 AS num
    FROM 
        #YourTableName a
    UNION ALL
    SELECT 
        STUFF(yourColumn, CHARINDEX('_', yourColumn), 2, UPPER(SUBSTRING(yourColumn, CHARINDEX('_', yourColumn) + 1, 1))) AS a,
        CHARINDEX('_', yourColumn) AS b,
        num + 1 AS num
    FROM 
        CTE a
    WHERE    
        b > 0
)
SELECT 
    * 
FROM 
    CTE
WHERE 
    yourColumn IS NOT NULL
    AND CHARINDEX('_', yourColumn) = 0;
Improve this answer

2 Comments

When you post answers with links to resources with which you have an affiliation, you are expected to disclose that affiliation in the post. Please edit your answer to include your affiliation. Otherwise your post may be deleted. Please see How to not be a spammer.
Also, while this may be a correct answer, it’d be really useful to provide additional explanation of your code so developers can understand your reasoning—and not just a link to an explanation. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.