MS SQL: Create User Function to Remove Long Words

skij
skij used Ask the Experts™
on
I want a user function that will remove all words longer than 7 characters.

For example:
Select dbo.RemoveLongWords('Hello Supercalifragilisticexpialidocious 01234567890 World 123 @#$!@# pneumonoultramicroscopicsilicovolcanoconiosis')

Open in new window

Should return:

Hello World 123 @#$!@#

ALTER Function [dbo].[RemoveLongWords](@Temp NVarChar(MAX))
Returns NVarChar(MAX)
AS
Begin

...???...

End

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2013

Commented:
Is it a homework?

Author

Commented:
@chaau: I've been working longer than you have been living :-)  It is not homework.
Top Expert 2013

Commented:
I don't care, in fact, I don't care at all. Good luck with your life with such attitude
Software Team Lead
Commented:
you may customize:

ALTER Function [dbo].[RemoveLongWords](@Temp NVarChar(MAX))
Returns NVarChar(MAX)
AS
Begin

Declare @separator varchar(2) = ' ';
Declare @v NVarChar(MAX);

with tokens(p, a, b) AS (
    select 
        cast(1 as bigint), 
        cast(1 as bigint), 
        charindex(@separator, @Temp)
    union all
    select
        p + 1, 
        b + 1, 
        charindex(@separator, @Temp, b + 1)
    from tokens
    where b > 0
)
Select 
@v = Stuff(  
(
	Select ' ' + item from 
	(
		select
			p-1 ItemIndex,
			substring(
				@Temp, 
				a, 
				case when b > 0 then b-a ELSE LEN(@Temp) end) 
			AS Item
		from tokens
	) a
	where len(item) <= 7
	For XML PATH ('')
), 1, 1, '')

return @v

End

Open in new window

Author

Commented:
This works perfectly, thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial