String and unique substring - 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)

String and unique substring

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-09-03 : 21:26:56

Hello,

I have a column that looks likeL

"White cat dog black cat dog etc".

I want to get a new string
"White cat dog black etc".

That means to remove duplicate strings.

Thanks for your idea.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-03 : 21:35:16

[code]DECLARE @STR varchar(50),
@new varchar(50)

SELECT @STR = 'White cat dog black cat dog etc'

SELECT @new = ISNULL(@new + ' ', '') + Data
FROM
(
SELECT Data, RowID = MIN(RowID)
FROM dbo.fnParseList(' ' , @STR)
GROUP BY Data
)d
ORDER BY RowID

SELECT @new[/code]

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-09-05 : 07:10:49

Thanks. But fnParseListis too big.
Is there a simple way?
For instance, convert the sentence into a rowset, then using distinct?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-05 : 08:11:41

quote:
Originally posted by zhshqzyc

Thanks. But fnParseListis too big.
Is there a simple way?
For instance, convert the sentence into a rowset, then using distinct?

what do you mean too big ?

quote:
convert the sentence into a rowset, then using distinct?

that's what the function is doing. Convert to rows

KH
[spoiler]Time is always against us[/spoiler]

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-09-05 : 09:18:07

How to call the function?
I got an error.


Msg 156, Level 15, State 1, Procedure fnParseString, Line 39
Incorrect syntax near the keyword 'DECLARE'.




CREATE FUNCTION dbo.fnParseString
(
	@Section SMALLINT,
	@Delimiter CHAR,
	@Text VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS

BEGIN
	DECLARE	@NextPos SMALLINT,
		@LastPos SMALLINT,
		@Found SMALLINT
	
	SELECT	@NextPos = 0,
		@Found = 0
	
	IF @Section > 0
		SELECT	@Text = REVERSE(@Text)
	
	WHILE @NextPos <= DATALENGTH(@Text) AND @Found < ABS(@Section)
		SELECT	@LastPos = @NextPos,
			@NextPos =	CASE
						WHEN CHARINDEX(@Delimiter, @Text, @LastPos + 1) = 0 THEN DATALENGTH(@Text) + 1
						ELSE CHARINDEX(@Delimiter, @Text, @LastPos + 1)
					END,
			@Found = @Found + 1
	
	IF @Found <> ABS(@Section) OR @Found = 0 OR @Section = 0
		SELECT	@Text = NULL
	ELSE
		SELECT	@Text = SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)
	
	RETURN CASE WHEN @Section < 0 THEN @Text ELSE REVERSE(@Text) END
END

DECLARE @STR varchar(250)
DECLARE @new varchar(50)

SET	@STR = 'White cat dog black cat dog etc'

SELECT	@new = ISNULL(@new + ' ', '') + Data
FROM
(
	SELECT	Data, RowID = MIN(RowID)
	FROM	dbo.fnParseList(' ' , @STR)
	GROUP BY Data
)d
ORDER BY RowID

SELECT	@new

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-07 : 00:38:25

quote:

Originally posted by zhshqzyc

How to call the function?
I got an error.


Msg 156, Level 15, State 1, Procedure fnParseString, Line 39
Incorrect syntax near the keyword 'DECLARE'.




CREATE FUNCTION dbo.fnParseString
(
	@Section SMALLINT,
	@Delimiter CHAR,
	@Text VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS

BEGIN
	DECLARE	@NextPos SMALLINT,
		@LastPos SMALLINT,
		@Found SMALLINT
	
	SELECT	@NextPos = 0,
		@Found = 0
	
	IF @Section > 0
		SELECT	@Text = REVERSE(@Text)
	
	WHILE @NextPos <= DATALENGTH(@Text) AND @Found < ABS(@Section)
		SELECT	@LastPos = @NextPos,
			@NextPos =	CASE
						WHEN CHARINDEX(@Delimiter, @Text, @LastPos + 1) = 0 THEN DATALENGTH(@Text) + 1
						ELSE CHARINDEX(@Delimiter, @Text, @LastPos + 1)
					END,
			@Found = @Found + 1
	
	IF @Found <> ABS(@Section) OR @Found = 0 OR @Section = 0
		SELECT	@Text = NULL
	ELSE
		SELECT	@Text = SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)
	
	RETURN CASE WHEN @Section < 0 THEN @Text ELSE REVERSE(@Text) END
END
GO
DECLARE @STR varchar(250)
DECLARE @new varchar(50)

SET	@STR = 'White cat dog black cat dog etc'

SELECT	@new = ISNULL(@new + ' ', '') + Data
FROM
(
	SELECT	Data, RowID = MIN(RowID)
	FROM	dbo.fnParseList(' ' , @STR)
	GROUP BY Data
)d
ORDER BY RowID

SELECT	@new

i guess you were trying to run them together. in which seperate function creation part from invocation by means of GO as above

dineshrajan_it
Posting Yak Master

217 Posts

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

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')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-12 : 02:20:59

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.63"
N 56°04'39.26"

ranganath
Posting Yak Master

209 Posts

Posted - 2008-09-12 : 02:37:35

Hi,

Try with this

Declare @data Varchar(200), @delimiter NVARCHAR(5), @strResult varchar(8000)
Set @data = 'White cat dog black cat dog etc'
set @delimiter = ' '

DECLARE @textXML XML;
SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML);
Declare @Temp Table (Id int Identity(1,1), Data Varchar(128))
Insert Into @Temp
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T (split)

SELECT @strResult = COALESCE(@strResult+' ', '') + Data
From (
Select Data, Min(Ranks) as Row
From ( Select Data, Row_Number()Over (Order BY Id ) As Ranks
From @Temp)A
Group By Data )B
order By Row

SELECT @strResult as 'Result'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources