Strings give me grief! - 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

Old Forums

CLOSED - General SQL Server

Strings give me grief!

Author

Topic

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-03 : 06:03:27

This is the text that gets passed to my proc from an external app for which I have no control over. The text includes the where clause.

' Where asm_no like ''%BZ82-X-05T-01-3%'' AND start_date BETWEEN ''20050601'' AND ''20050601 23:59:59 '' AND end_date BETWEEN ''20050602'' AND ''20050602 23:59:59 '''

For my purposes I extract the bits I need for processing and I always end up spend yonks trying to figure out the correct calculation to get to the bits I need

I have three variables like so;

SET @PosItem = PATINDEX ( '%asm_no%' , @WhereClause )

SET @PosStart = PATINDEX ( '%start_date%' , @WhereClause )

SET @PosEnd = PATINDEX ( '%end_date%' , @WhereClause )

I manage to obtain BZ82-X-05T-01-3 like this,

SELECT @Item_No = SUBSTRING(@WhereClause, 22, @PosStart-29)

Now to try to get the start date I need to start at the character after the 3 in @Item_no, so this will be @PosStart-29

I then need to add to this the number of charcters to the start of the start date, which is 29. But @PosStart-29+29 = @PosStart so I get the wrong start place.

SELECT @Start_Date = SUBSTRING(@WhereClause, @PosStart, 8)

Now I KNOW I can't see the wood for the trees and it is Friday and my brain hurts......help please???

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-03 : 06:39:34

Aha, I think I see my error!

This works, but sure is ugly!! Any better offers?

SELECT @Start_Date = SUBSTRING(@WhereClause, (@PosStart-29)+49, 8)

Kristen
Test

22859 Posts

Posted - 2005-06-05 : 03:01:14

Why's it ugly?, looks alright to me. You could add "-29" and "+49", making "+20", and simplify it to that - but I'd still comment it with the calculation method

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-05 : 08:56:19

Here is another approach.
It picks up arguments from a string that has the following format:

"blabla 'arg1' blabla 'arg2' blabla 'arg3' blabla"

In above case we will pick out: arg1, arg2, arg3

declare @arguments table(arg smallint primary key, value varchar(200))

declare @WhereClause varchar(200)
set @WhereClause = ' Where asm_no like ''%BZ82-X-05T-01-3%'' AND start_date BETWEEN ''20050601'' AND ''20050601 23:59:59 '' AND end_date BETWEEN ''20050602'' AND ''20050602 23:59:59 '''

declare @pos smallint, @n smallint
select @pos = -1, @n = 0

while(1=1)
begin
	select @pos = charindex('''',@WhereClause,@pos+1), @n = @n + 1
	if @pos = 0
		break
	if @n % 2 = 1
		insert @arguments(arg,value)
		select (@n+1)/2, substring(@WhereClause,@pos+1,charindex('''',@WhereClause,@pos+1)-@pos-1)
end

select * from @arguments
/*
arg    value
------ ------------------
1      %BZ82-X-05T-01-3%
2      20050601
3      20050601 23:59:59
4      20050602
5      20050602 23:59:59
*/

rockmoose

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-06 : 10:30:01

Kristen, I guess I just think my code is crap? maybe not...

rockmoose, thnx I will copy this to my PC and have a play with it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources