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