SMALL FUNCTION - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SMALL FUNCTION

Author

Topic

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-09-12 : 01:59:19

CREATE FUNCTION DUPTEXTREMOVALFUNCTION
(
@DUPTEXT VARCHAR(200)
)
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @SPLITTEXT VARCHAR(200)
DECLARE @ORGTEXT VARCHAR(200)
DECLARE @WHITESPACEPOST INT

SET @ORGTEXT = ''

IF(RIGHT(@DUPTEXT,1) <> SPACE(1))
SET @DUPTEXT = @DUPTEXT + SPACE(1)
SET @WHITESPACEPOST = CHARINDEX(SPACE(1),@DUPTEXT)

WHILE(@WHITESPACEPOST > -1)
BEGIN
SET @SPLITTEXT =LEFT(@DUPTEXT,@WHITESPACEPOST)
IF((@ORGTEXT NOT LIKE '%' + @SPLITTEXT + '%') OR (@ORGTEXT = ''))
BEGIN
SET @ORGTEXT = LTRIM(@ORGTEXT + SPACE(1) + @SPLITTEXT)
END
SET @DUPTEXT = SUBSTRING(@DUPTEXT,@WHITESPACEPOST + 1,LEN(@DUPTEXT))
SET @SPLITTEXT = LTRIM(RTRIM(@SPLITTEXT))
IF(LTRIM(RTRIM(@SPLITTEXT)) = '')
BEGIN
SET @ORGTEXT = @ORGTEXT + SPACE(1) + @DUPTEXT
BREAK
END
SET @WHITESPACEPOST = CHARINDEX(SPACE(1),@DUPTEXT)
END
RETURN @ORGTEXT
END

SELECT 'DUPLICATE TEXT'='WHITE BLACK BROWN WHITE','VERIFIED TEXT'= DBO.DUPTEXTREMOVALFUNCTION('WHITE BLACK BROWN WHITE')

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-09-12 : 02:01:54

GUYS PLEASE NEGLECT THIS TOPIC.