Menu
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 ,' ') 
881
CHARINDEX('substring', col) will return the index of the first occurrence of 'substring' in the column. STUFF then replaces this occurrence with 'replacement' .
SQL Server REPLACE() Function The REPLACE() function replaces all occurrences of a substring within a string, with a new substring. Note: The search is case-insensitive.
Searching from the start of a string expression. This example returns the first location of the string is in string This is a string , starting from position 1 (the first character) of This is a string . SELECT CHARINDEX('is', 'This is a string');
In a view, you could do it like: select case when col1 like '00%' then stuff(col1, 1, 2, '11') else col1 end from YourTable; Live example at SQL Fiddle. Just a note, the substring should be "substring(col1, 3, len(col1)-2)"because you want to start at 3rd character and the characters are numbered from 1, not 0.
Code language: SQL (Structured Query Language) (sql) In this syntax: input_string is any string expression to be searched. substring is the substring to be replaced. new_substring is the replacement string. The REPLACE() function returns a new string in which all occurrences of the substring are replaced by the new_substring.
In SQL Server, you can use the T-SQL REPLACE () function to replace all instances of a given string with another string. For example, you can replace all occurrences of a certain word with another word.
The REPLACE function will search for all occurrences of the old_substring and replace it with the new_string. The following statement replaces all the occurrences of bar with foo so the result is bar bar bar. Note that the REPLACE function searches for the substring in the case sensitive manner.
Perform search/replace for only the first occurrence of a character in MySQL table records? You can achieve this with the help of CONCAT () along with REPLACE () function.
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'.
104
it seems you miss 2% preceding and trailing to the target string
please try:
select STUFF(@stringhere, PATINDEX('%' + @stringtofind + '%', @stringhere), LEN(@stringtofind), ' ') If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
