separate string - 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)

separate string

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-03-27 : 06:37:52

Hi,
There is a slight error in this sql query below.
It basically takes a string and splits the words into the ones which have not at the beginning and the ones which do not have NOT at the beginning.

Example of a string being passed to process;

'NOT "hello you" AND NOT "office" OR "outside home" AND NOT "inside house" or "maybe inside" and "sometimes" OR NOT "outside"'

Therefore result should be

IncludedList --> "outside home" or "maybe inside" and "sometimes"
NotList --> '"hello you" AND "office" AND "inside house" OR "outside"'

At present the result is as follows which is in-correct:

IncludedList --> "outside home" or "maybe inside" or "sometimes"
NotList --> '"hello you" or "office" or "inside house" OR "outside"'

How can I update the query to do what I am after please?

Thank you

The function is:

DECLARE @SearchStr VARCHAR(MAX),
@NotList VARCHAR(MAX),
@IncludeList VARCHAR(MAX),
@TempStr VARCHAR(MAX),
@Index BIGINT,
@TextToCopy VARCHAR(MAX)

SET @SearchStr = 'NOT "hello you" AND NOT "office" OR "outside home" AND NOT "inside house" OR "maybe inside"'
SET @NotList = ''
SET @IncludeList = ''

SET @Index = CHARINDEX('"', @SearchStr)

WHILE (@Index <> 0)
BEGIN
SET @TempStr = SUBSTRING(@SearchStr, 1, @Index)
SET @SearchStr = SUBSTRING(@SearchStr, @Index+1, LEN(@SearchStr))

SET @TextToCopy = SUBSTRING(@SearchStr, 1, CHARINDEX('"', @SearchStr)-1)
SET @SearchStr = SUBSTRING(@SearchStr, LEN(@TextToCopy)+2, LEN(@SearchStr))

IF (CHARINDEX('NOT', UPPER(@TempStr)) <> 0)
BEGIN
SET @NotList = @NotList + '"' + @TextToCopy + '" OR '
END
ELSE
BEGIN
SET @IncludeList = @IncludeList + '"' + @TextToCopy + '" OR '
END

SET @Index = CHARINDEX('"', @SearchStr)
END

IF (LEN(@NotList) > 0)
SET @NotList = SUBSTRING(@NotList, 1, LEN(@NotList)-3)

IF (LEN(@IncludeList) > 0)
SET @IncludeList = SUBSTRING(@IncludeList, 1, LEN(@IncludeList)-3)

SELECT @NotList
SELECT @IncludeList

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-03-27 : 13:20:29

Hello,
Can anyone help with this at all?
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources