I have varchar column in my table which has some data like:
"#306 loream ipsum aaabbbqqqcc"
"#302 loream ipsum aaabbbceefec"
"#330 loream ipsum aaabbbccsds"
I want to replace #3XX like regex syntax in C# or JavaScript.
How to implement this in SQL Server?
You can make use of the STUFF function to achieve this.
Declare @String Varchar(100) = '#306 loream ipsum aaabbbqqqcc'
SELECT STUFF(@String , 1,5,'')
Result: loream ipsum aaabbbqqqcc
You can make use of the RIGHT function to achieve this.
Declare @String Varchar(100) = '#306 loream ipsum aaabbbqqqcc'
SELECT RIGHT(@String , LEN(@String) - 5)
RESULT: loream ipsum aaabbbqqqcc
You can use substring if it is always first 4 characters..
declare @str varchar(max)
set
@str='#306 loream ipsum aaabbbqqqcc'
select substring(@str,5,len(@str))
if it is possible to split string by space then follow below one
Declare @str Varchar(100) = '#306 loream ipsum aaabbbqqqcc'
set @str =substring( @str ,CHARINDEX(' ', @str) , len(@str) - CHARINDEX(' ', @str)+1)
In this one you don't need any default length or default replacement text.
To Remove it , use this query.Since the length of the string which you want to remove is always 4.
UPDATE table-name SET column-name = RIGHT(column-name, LEN(column-name) - 4)
But run only one time because it is UPDATE query
For MySql you have to use the length() function like that,
UPDATE table-name SET column-name = RIGHT(column-name, LENGTH(column-name) - 4)
Replace function will be a good choice since the string to be replaced is dynamic.