juststeve
asked on
Using 'LEFT' in a WHERE clause
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
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*
> 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*
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.
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.
ASKER
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_smar ter_this_w ould_be_a_ descrete_f ield^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.
Field Data is in this form:
11.11.11.11^If_I_were_smar
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
:)
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
:)
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!!
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!!
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