searching a substring with delimiter in ntext fiel

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 2000 Forums

SQL Server Development (2000)

searching a substring with delimiter in ntext fiel

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-09-18 : 10:33:02

i have an ntext variable in a STORED PROCUDURE.
this variable contains a delimited string,say with Delimiter= "|"
for example :
aaa|bbb|ccc .........
the problem is how to extract all this sub string?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 11:17:28

Use Parsevalues function found here:-

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

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-09-18 : 11:38:15

that function supports only varchar(8000) and not ntext

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 11:47:46

quote:
Originally posted by pelegk2

that function supports only varchar(8000) and not ntext

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

cast it to varchar and then use function. Also make sure your text length is always less than 4000.

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-09-18 : 17:07:34

but if the ntext has 12000 chars - iwill loss data!

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-21 : 02:33:47

quote:
Originally posted by pelegk2

but if the ntext has 12000 chars - iwill loss data!

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

have you tried modifying the function to accept text data

CREATE FUNCTION ParseValues  
(@String ntext  
)  
RETURNS @RESULTS TABLE  
(ID int identity(1,1),  
Val varchar(1000)
)  
AS  
BEGIN  
DECLARE @Value varchar(100)  
  
WHILE @String is not null  
BEGIN  
SELECT @Value=CASE WHEN CHARINDEX('|',@String) >0 THEN SUBSTRING(@String,1,CHARINDEX('|',@String)-1) ELSE @String END,  
@String=CASE WHEN CHARINDEX('|',@String) >0 THEN SUBSTRING(@String,CHARINDEX('|',@String)+1,DATALENGTH(@String)) ELSE NULL END  
INSERT INTO @RESULTS (Val)   
SELECT @Value   
END  
RETURN  
END

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-09-21 : 03:03:58

again
try your code on sql server 2000 u will get an error that you can't set an ntext value

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-21 : 03:50:22

quote:
Originally posted by pelegk2

again
try your code on sql server 2000 u will get an error that you can't set an ntext value

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

which line its giving error. i'm at home and cant test it now

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-09-21 : 06:45:11

[code]
SELECT @Value=CASE WHEN CHARINDEX('|',@String) >0 THEN SUBSTRING(@String,1,CHARINDEX('|',@String)-1) ELSE @String END,
@String=CASE WHEN CHARINDEX('|',@String) >0 THEN SUBSTRING(@String,CHARINDEX('|',@String)+1,DATALENGTH(@String)) ELSE NULL END

[/code]

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Kamyar
Starting Member

1 Post

Posted - 2009-11-25 : 02:58:10

In case anyone still wanna do this using ntext:
We have MessageIDs as ntext which is the delimited string. the table: @ParsedMessageIDs will contain all the values in separate rows.


DECLARE @ParsedMessageIDs TABLE
      (
            MessageID int
      )
    DECLARE @MessageIDsChunk NVARCHAR(4000), @MessageID varchar(10), @Pos INT, @Itr INT, @TrimIndex int
    SET @Itr = 0
    SET @MessageIDSChunk  = SUBSTRING( @MessageIDs, @Itr, @Itr + 4000 )
    WHILE LEN(@MessageIDsChunk) > 0
    BEGIN
            SET @TrimIndex = CHARINDEX(',',REVERSE( @MessageIDsChunk ), 1 );
            SET @MessageIDsChunk = SUBSTRING(@MessageIDsChunk,0, 4000-@TrimIndex)
            SET @Itr = @Itr - @TrimIndex
            SET @MessageIDsChunk = LTRIM(RTRIM(@MessageIDsChunk))+ ','
            SET @Pos = CHARINDEX(',', @MessageIDsChunk, 1)
            IF REPLACE(@MessageIDsChunk, ',', '') <> ''
            BEGIN
                  WHILE @Pos > 0
                  BEGIN
                        SET @MessageID = LTRIM(RTRIM(LEFT(@MessageIDsChunk, @Pos - 1)))
                        IF @MessageID <> ''
                        BEGIN
                              INSERT INTO @ParsedMessageIDs (MessageID) VALUES (CAST(@MessageID AS int)) --Use Appropriate conversion
                        END
                        SET @MessageIDsChunk = RIGHT(@MessageIDsChunk, LEN(@MessageIDsChunk) - @Pos)
                        SET @Pos = CHARINDEX(',', @MessageIDsChunk, 1)
                  END
            END      
            SET @Itr = @Itr + 4000;
            SET @MessageIDSChunk  = SUBSTRING( @MessageIDs, @Itr, @Itr + 4000 )
      END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources