Please help me TWEAK my Select Statement

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

Development Tools

Other Development Tools

Please help me TWEAK my Select Statement

Author

Topic

PaulMcGuire
Starting Member

6 Posts

Posted - 2004-03-19 : 05:36:15

I have built this SQL statement which should
create the RS I need.

strSQL1 = "SELECT [INVENTRY MASTER].BOX_NO FROM [INVENTRY MASTER] WHERE
Left([INVENTRY MASTER].BOX_NO, PatIndex('%821%', [INVENTRY MASTER].BOX_NO) -
1) NOT LIKE '%[1-9]%' AND [INVENTRY MASTER].BOX_NO LIKE '%821%';"

This Line:

objRS1.Open strSQL1, objConn

Causes this error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid length parameter
passed to the substring function.

I have searched google, and found reference to the error meaning it found a
space in the first position. I tried adding LTRIM into my statement to cure
it but it made no difference, I may be barking up the wrong tree so to speak
;) but I cant find any other information on it.

If anyone has any ideas why this statement does not work I'd be very
grateful, the project has to be completed today, and this is the last thing
to get working now!

Ta

Paul McGuire

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-19 : 05:58:23

What is the PATINDEX part for? IIRC PATINDEX returns 0 when there is no match. 0-1 = -1 which makes the expression invalid.

--Frank
http://www.insidesql.de

PaulMcGuire
Starting Member

6 Posts

Posted - 2004-03-19 : 06:15:10

Let me expand on what I am trying to achieve to see if you can help further.

imagine a table collumn

BOX_NO
-------------------------
JHIS 0000821
JHIS 0000821a
JHIS 0000821b
JHIS 00821
JHIS 00821a
JHIS 0001821
JHIS 0001821a
JHIS 0001821b
JHIS 01821
JHIS 01821a

A user on the internet page I am writing will enter 821 because he wants a list of the boxes:

JHIS 0000821
JHIS 0000821a
JHIS 0000821b
JHIS 00821

The JHIS is the users account number the leading 0's can vary which is what has caused the need to use PatIndex in the way which I have? Is there another way?

All the user knows is box number JHIS 0000821 is box number 821 if he wanted JHIS 0001821 he would enter 1821 and expect to get these boxes returned:

JHIS 0001821
JHIS 0001821a
JHIS 0001821b
JHIS 01821
JHIS 01821a

Any ideas on the best way to do this? Am i on the right lines?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-19 : 06:24:24

Have you tried Replace???

select replace('00000821', '0', '')

Returns 821

only thing is, it'll replace the 0's in the middle of the numbers with blanks too.

Duane.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-19 : 06:39:03

How about Matching both by removing leading zeros.

Could something like this work?

DECLARE @String VARCHAR(100)

SET @String = '0000008021'
SELECT RIGHT(@String, LEN(@String) + 1 - (PATINDEX('%[1-9]%',replace(@String, '0', '^'))))

--************** Returns 8021

Duane.

PaulMcGuire
Starting Member

6 Posts

Posted - 2004-03-19 : 06:42:22

That would not help completely though because if a user searched 821 I only want 0000821 boxes and not 0001821 boxes etc. Thats the problem I am having the trouble finding a route around.

PaulMcGuire
Starting Member

6 Posts

Posted - 2004-03-19 : 06:44:45

ignore that last message that was answering your replace post. I will try this latest Idea and thanks for the help. I will let you know how I'm getting on. Keep the ideas coming I have about 2 hours to get this working before my boss removes my head :) !!

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-19 : 06:49:08

Am I understanding this correctly that you try to search for 821? And that 821<>1821? And you can only search in this column for that number? Is it some kind of key?

To be honest, I'm a little bit confused. Could it be your data model is a little bit flawed?
Maybe I only need more information.

--Frank
http://www.insidesql.de

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-19 : 06:51:57

Forget my last post. Save your head is more important now

--Frank
http://www.insidesql.de

PaulMcGuire
Starting Member

6 Posts

Posted - 2004-03-19 : 07:20:01

I think I'm getting there, I have a select statement that appears to be working... oh and yes the data model is flawed but it was not designed to be used in this way, but now management have decided they want everything :) and on my head be it. so I'm gonna get back to it!

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-19 : 07:33:38

now management have decided they want everything

How suprising!

--Frank
http://www.insidesql.de

PaulMcGuire
Starting Member

6 Posts

Posted - 2004-03-19 : 08:12:24

It was so simple in the end I think I got bogged down in the detail.

WHERE BOX_NO LIKE "'%0821[a-z]%' or BOX_NO LIKE "'%0821'

by prefixing a '0' to the front it does not return any of the 0001821 boxes and by using the [a-z] and the or staement it stops 008210 boxes being returned.

Sorted. Thanks for all your help guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources