How can I replace 'a' to blank?

`Name`              `ID`
----------------------------------
`b,c,d,e,abb,a`     `1`
`b,c,d,a,e,abb`     `2`  
`a,b,c,d,a,e,abb`   `3`

CodePudding user response：

One way to do it would be to add a , to the beginning and end of each Name, then replace every occurence of ',a,' with ',', then trim the result of the ,:

update table_name
set Name = trim(',' from replace(concat(',', Name, ','), ',a,', ','));

Fiddle

Or if you just want to do a select without changing the rows:

select trim(',' from replace(concat(',', Name, ','), ',a,', ',')) as Name, ID
from table_name;

To address @Iptr's comment, if there can be consecutive a such as a, a, ..., you could use STRING_SPLIT to get rows from comma-separated values, then filter out where the value is a, then STRING_AGG and group by to get the comma separated values back:

select ID, STRING_AGG(u.Value, ',') as Name
from table_name
cross apply STRING_SPLIT (Name, ',') u
where Value <> 'a'
group by ID

Fiddle

CodePudding user response：

Here is a solution based on tokenization via XML/XQuery.

It will work starting from SQL Server 2012 onwards.

Steps:

1. We are tokenizing a string of tokens via XML.
2. XQuery FLWOR expression is filtering out the 'a' token.
3. Reverting it back to a string of tokens.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(1000));
INSERT INTO @tbl (tokens) VALUES
('b,c,d,e,abb,a'),
('b,c,d,a,e,abb'),
('a,b,c,d,a,e,abb');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ',';

SELECT t.* 
, REPLACE(c.query('
   for $x in /root/r/text()
   return if ($x = "a") then ()
      else data($x)
   ').value('.', 'VARCHAR(MAX)'), SPACE(1), @separator) AS Result
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA['   
        REPLACE(tokens, @separator, ']]></r><r><![CDATA[')   
        ']]></r></root>' AS XML)) AS t1(c);

Output

 ---- ----------------- ------------- 
| ID |     tokens      |   Result    |
 ---- ----------------- ------------- 
|  1 | b,c,d,e,abb,a   | b,c,d,e,abb |
|  2 | b,c,d,a,e,abb   | b,c,d,e,abb |
|  3 | a,b,c,d,a,e,abb | b,c,d,e,abb |
 ---- ----------------- ------------- 

CodePudding user response：

Try as follow:

select Replace(name, N'a', N'') as RepName , ID from yourTable

CodePudding user response：

Try this.

SELECT ID,Name, REPLACE(Name, 'a', ' ') FROM tableName;