Is a query possible for this (without Loop) ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Is a query possible for this (without Loop) ?

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-06-03 : 09:10:05

Hi Team,

I have a column that has place holders specified between [[ and ]]

E.g . 'On [[Date]], [[EmpID]] was on leave'

I need to list out all such place holders (Date, EmpID). These place holder may be lying in the beginning middle or at the end – or even my be absent in one record.

I wrote the following procedure which will meet the requirement. However, is there any easy/compact query possible, in SQL, for this purpose?

Note: I am looking for a query that will not use Loops.

CREATE TABLE #Template (TemplateID INT, Body VARCHAR(100))
INSERT INTO #Template (TemplateID,Body) VALUES (1,'[[Date]] def ff [[EmpID]]')
INSERT INTO #Template (TemplateID,Body) VALUES (2,'Cheeran')

CREATE TABLE #Result (Word VARCHAR(100))

DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(TemplateID) FROM #Template)

DECLARE @Counter INT
SET @Counter = 0

WHILE @Counter < @RowCount
BEGIN

SET @Counter = @Counter+1
DECLARE @Body VARCHAR(100)
SET @Body = (SELECT Body FROM #Template WHERE TemplateID = @Counter)

DECLARE @Length INT
SET @Length = (SELECT LEN (@Body))

DECLARE @Iterator INT
SET @Iterator = 0

DECLARE @CurrentCharacter CHAR(1)
DECLARE @PreviousCharacter CHAR(1)
DECLARE @Word VARCHAR(100)
DECLARE @Flow VARCHAR(10)

WHILE @Iterator < @Length
BEGIN

SET @Iterator = @Iterator + 1
SET @CurrentCharacter = (SELECT SUBSTRING (@Body,@Iterator,1))

PRINT @Iterator
PRINT 'Current '+ @CurrentCharacter
PRINT 'Previous '+ ISNULL(@PreviousCharacter,'')

IF (@PreviousCharacter = ']' AND @CurrentCharacter = ']')
BEGIN
SET @Flow = 'Off'

IF @Word IS NOT NULL
BEGIN
INSERT INTO #Result (Word) VALUES (@Word)
END

SET @Word = NULL

END

IF ((@PreviousCharacter = '[' AND @CurrentCharacter = '['))
BEGIN
SET @Flow = 'Running'
END

IF @Flow = 'Running' AND @CurrentCharacter NOT IN ( '[', ']')
BEGIN

IF @Word IS NULL
BEGIN
SET @Word = @CurrentCharacter
END
ELSE
BEGIN
SET @Word = @Word + @CurrentCharacter
END
END

SET @PreviousCharacter = @CurrentCharacter
END

END

SELECT * FROM #Result

DROP TABLE #Template
DROP TABLE #Result

Thanks
Lijo Cheeran Joseph

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-03 : 10:13:59

This doesn't account for where there are no [[]], but you can modify your code to handle that

select substring(body,PATINDEX('%[[[]%',body)+2,PATINDEX('%]]%',body)-3)

,reverse(substring(reverse(body),PATINDEX('%]]%',reverse(body))+2,PATINDEX('%[[]%',reverse(body))-3))
from #template
where body like '%[[[]%[[[]%'

Jim

Everyday I learn something that somebody else already knew

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-03 : 10:25:40

[code]

DECLARE @string VARCHAR(8000)

SELECT @string = 'On [[Date]],[[EmpID]] was on leave'
SELECT @string=replace(replace(@string ,'[[','/~'),']]','/~')

select left(data,charindex('/',data)-1) as data from
(
SELECT
data = SUBSTRING(@string, n, CHARINDEX('~', @string + '~', n ) - n)
FROM
(
SELECT number as n FROM master..spt_values
where type='p'
) numbers
WHERE
SUBSTRING( '~' + @string, n, 1 ) = '~'
) as t
where data like '%[a-z]/'

[/code]

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-03 : 10:39:27

Have you seen my previous reply?

Madhivanan

Failing to plan is Planning to fail

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-06-03 : 11:05:35

quote:
Originally posted by madhivanan

Have you seen my previous reply?

Thank you Madhivanan

It works. But it does not take special characters. I was trying to dissect the logic and study it.

Following is what I have reached now.
Thanks again..

DECLARE @string VARCHAR(8000)
SELECT @string = 'On [[Date]],[[Emp5ID]] was on leave [[#$!]]'

SELECT @string = REPLACE(REPLACE(@string ,'[[','/~'),']]','/~')

SELECT LEFT(data,CHARINDEX('/',data)-1) as data
FROM
(
SELECT data = SUBSTRING(@string, n, CHARINDEX('~', @string + '~', n ) - n)
FROM
(
SELECT number as n
FROM master..spt_values
WHERE TYPE='p'
) numbers
WHERE SUBSTRING( '~' + @string, n, 1 ) = '~'
) as t
WHERE (NULLIF(data,'') IS NOT NULL)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-03 : 11:31:13

Could you post some examples of the strings you'll actually be searching and what the expected output is?

This 'On [[Date]], [[EmpID]] was on leave' is different from
'[[Date]] def ff [[EmpID]]' which is different from
'On [[Date]],[[Emp5ID]] was on leave [[#$!]]'

Are they all possible in your data set?

Jim

Everyday I learn something that somebody else already knew

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-06-03 : 12:40:53

Anything other than the following is a special character for this scenario
1) Alphabtes
2) Digits
3) Space
4) [
5) ]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-03 : 13:02:42

select count(*) -- substring(body,n,1), ascii(substring(body,n,1))

from
#template
cross apply

(select number as n from master..spt_values where [type] = 'P' and number < len(body)) a

where
ascii(substring(body,n,1)) not in (32,93) -- space and ]
and ascii(substring(body,n,1))not between 65 and 91 --A-Z
and ascii(substring(body,n,1)) not between 97 and 122 -- a-z
and ascii(substring(body,n,1)) not between 59 and 57 -- 0-9

jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources