SQL to update rows to remove words with less than N characters-CodePudding

I have a TAGS column in my Products table in SQL Server. In my web project I split them with space, for example:

"web web_design website website_design"
   =>  1.web  2. web_design 3. website ......

How I can remove words with less than N characters from tags? Is it possible with a regex?

For example if N=4 so "web" will be removed from my example and the rest remains.

CodePudding user response：

I will give a solution to do this without changing your design at the bottom of this answer,

but I really think you should fix the design, here is an example on how to do that.

It starts from a table called "mytable" that has your "tag" column with all the data,
then it creates a detail table and populates it with the splitted values from your tag column
and then it is very easy to do what you want to do

create table tags (id int identity primary key, mytable_id int, tag varchar(100))

insert into tags (mytable_id, tag)
select t.id,
       value
from   mytable t
  cross apply string_split(t.tag, ' ')

alter table mytable drop column tag

See a complete example in this dbfiddle

EDIT

if you need to show it again as if it where in one table, you can use string_agg like this

select m.id,
       m.name,
       ( select string_agg(t.tag, ' ')
         from   tags t
         where  t.mytable_id = m.id
       ) as tags
from   mytable m

You can see this at work in this dbfiddle

EDIT 2

And if you really want to stick to your design, here is how you can remove the words from your tag column

But I recommend not doing this, as you can see in the examples above it is not so hard to fix the design and create a new table to hold the tags.

update m
set    m.tag = 
       ( select string_agg(value, ' ')
         from   mytable t
           cross apply string_split(m.tag, ' ')
         where len(value) > 3
         and   t.id = m.id
       ) 
from   mytable m

Look at this dbfiddle to see it in action