Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Using 'LEFT' in a WHERE clause

Posted on 2003-10-24
10
Medium Priority
?
343 Views
Last Modified: 2008-02-01
I can use this expression in a select statement to isolate the part of the field I'm interested in: LEFT(strIPAddress, CHARINDEX('^', strIPAddress)-1)

But when I use the same expression in a WHERE clause I get error:
 Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.


full query text:
select top 25 * from mLogins where
LEFT(strIPAddress, CHARINDEX('^', strIPAddress)-1) =  '25.160.000.13'
 order by thedate desc
0
Comment
Question by:juststeve
  • 5
  • 3
  • 2
10 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9618205
This query works
select left(au_fname, charindex('^', au_lname) )
from authors

but sql below does not work

select left(au_fname, charindex('^', au_lname) -1 )
from authors


The reason is charindex('^', au_lname) -1 returns -1.
-1 is not a valid parameter for Left function.

Something like this will work.

select
CASE
   WHEN (charindex('^', au_lname) -1) = -1 THEN left(au_fname, charindex('^', au_lname)  )
   WHEN (charindex('^', au_lname) -1) > 0 THEN left(au_fname, charindex('^', au_lname)  )
   ELSE ''
  END
from authors


HTH

Namasi Navaretnam
0
 
LVL 19

Assisted Solution

by:Dexstar
Dexstar earned 400 total points
ID: 9618225
juststeve:

> full query text:
> select top 25 * from mLogins where
> LEFT(strIPAddress, CHARINDEX('^', strIPAddress)-1) =  '25.160.000.13'
>  order by thedate desc

The problem is there are some values where the ^ does not appear in strIPAddress.  In those cases, CHARINDEX returns 0.  If it returns 0, and you subtract 1 from it, then you're calling LEFT() with a length of -1, which is causing the error.  Try this instead:

     select top 25 * from mLogins where
     LEFT(strIPAddress,
          CASE WHEN CHARINDEX('^', strIPAddress) = 0 THEN 1
                ELSE CHARINDEX('^', strIPAddress)-1
          END) =  '25.160.000.13'
     order by thedate desc

In that case, if there is no ^, it will just use the value of 1 as the length parameter, which will return the whole string.

Hope that helps,
Dex*
0
 

Author Comment

by:juststeve
ID: 9618251
not quite following you Namasi... I my case the query with the '-1' _will work:

select top 5
LEFT(strIPAddress, CHARINDEX('^', strIPAddress)-1) as IPAddress
from mLogins

The problem is making that expression work inside the WHERE instead of the SELECT:
select top 25 * from mLogins
WHERE LEFT(strIPAddress, CHARINDEX('^', strIPAddress)-1) =  '25.160.000.13'

Now...I can work around this problem by changing the WHERE to:
WHERE LEFT(strIPAddress, CHARINDEX('^', strIPAddress)) =  '25.160.000.13^'

including the instance of the '^'. But why?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 19

Expert Comment

by:Dexstar
ID: 9618268
juststeve:

> The problem is making that expression work inside the WHERE instead of the SELECT

I'm pretty sure my previous post did exactly that.  Did this not work for you?
 
    select top 25 * from mLogins where
     LEFT(strIPAddress,
         CASE WHEN CHARINDEX('^', strIPAddress) = 0 THEN 1
                ELSE CHARINDEX('^', strIPAddress)-1
          END) =  '25.160.000.13'
     order by thedate desc

If it doesn't work, post the error message you get, and we'll fix it.  :)


Dex*
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9618307
select top 5
LEFT(strIPAddress, CHARINDEX('^', strIPAddress)-1) as IPAddress
from mLogins

sql above will not work if CHARINDEX('^', strIPAddress)-1 returns -1.
Because something like
SELECT LEFT('AAAA', -1)
will not work


Namasi.

0
 

Author Comment

by:juststeve
ID: 9618562
Dexstar...our messages crossed first time around or I would have added/assured that there are no 'not founds'.

Field Data is in this form:
11.11.11.11^If_I_were_smarter_this_would_be_a_descrete_field^but_I''m_not

So I'm looking to isolate all characters upto (and _excluding) the '^'. The 'where' works if I include the trailing/delimiting '^' character.

select top 5
LEFT(strIPAddress, CHARINDEX('^', strIPAddress)-1)
, strIPAddress as whatever from mLogins

The above returns expected value:  '11.11.11.11'


Bottom line:  the '-1' is honored in the SELECT clause as intention to subtract from the charindex the search character but doesn't  seem to be honored the same way in a WHERE clause. 'Not Found' is not coming into play in this instance.

0
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 600 total points
ID: 9619350
This query works for yo since you are selecting only the top 5 rows. For the top 5 rows expression strIPAddress, CHARINDEX('^', strIPAddress) -1  may not be returning -1.

select top 5
LEFT(strIPAddress, CHARINDEX('^', strIPAddress)-1), strIPAddress as whatever from mLogins

But If your sql is as below you will that error

select *
LEFT(strIPAddress, CHARINDEX('^', strIPAddress)-1), strIPAddress as whatever from mLogins

Simple Test
SELECT LEFT('AAA', -1) will return error.

When you add CHARINDEX('^', strIPAddress) -1 into where clause all rows will be scanned and a error condition is met. That is why you need to use case statement.

Condition 1: CHARINDEX('^', strIPAddress)-1 returns  (0-1) = -1  (In this case LEFT will not work)
Condition 2: CHARINDEX('^', strIPAddress)-1 returns  > 0

SQL below worked for me,

select * from employee
where LEFT(fname, CHARINDEX('e', fname)-1) =  'Ann' and
          CHARINDEX('e', fname) > 0



SQL below should work for you
select top 25 * from mLogins
where LEFT(strIPAddress, CHARINDEX('^', strIPAddress)-1) =  '25.160.000.13' and
          CHARINDEX('^', strIPAddress) > 0
           order by thedate desc


HTH

Namasi Navaretnam




0
 

Author Comment

by:juststeve
ID: 9619378
Ok...now I follow you explaintion about an error generated on a not-found condition. (Thankx for the clarification.) And I can run your sample against pubs. But when I try the same technique against my db I still get the same error reported in the first message:

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

This still works (returns expected records):
where LEFT(strIPAddress, CHARINDEX('^', strIPAddress)) =  '25.160.000.13^'

This still tosses the error:
where LEFT(strIPAddress, CHARINDEX('^', strIPAddress)-1) =  '25.160.000.13' and
CHARINDEX('^', strIPAddress) > 0
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9619461
Well, you get the idea!!!

May be try,
where LEFT(strIPAddress, CHARINDEX('^', strIPAddress)-1) =  '25.160.000.13' and
CHARINDEX('^', strIPAddress) > 1

I do not see why that will generate an error.?>!!?    :)

I tried sql below and works in pubs.

Update Employee
Set fname = 'Ann^asdfhh'
where emp_id  = 'A-R89858F'

select * from employee
where LEFT(fname, CHARINDEX('^', fname)-1) =  'Ann' and
          CHARINDEX('^', fname) > 0

:)
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9619570
where LEFT(strIPAddress, CHARINDEX('^', strIPAddress)) =  '25.160.000.13^'

is better than one below for performance reasons anyway!!

where LEFT(strIPAddress, CHARINDEX('^', strIPAddress)-1) =  '25.160.000.13' and
CHARINDEX('^', strIPAddress) > 1

So, lets go with that!!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question