LIKE function - 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

General SQL Server Forums

New to SQL Server Programming

LIKE function

Author

Topic

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-12 : 05:33:39

Hi all.
This query returns no value. is it possible to use a variable in LIKE function? if so, what should it be...

declare @divisionCODE varchar(10)
set @divisionCODE = '1001'

Select a.*, b.fullname, b.id, c.description as divisiondescription,
d.description as sectiondescription, e.branchname as branchname,
f.description as whdescription, g.description as positiondescription
From hremployees a, psdatacenter b, mscdivisions c,
mscsections d, mscbranches e, mscwarehouse f, mscpositions g
Where a.empdcno = b.dcno and a.divisioncode = c.divisioncode and

a.branchcode like '%@divisionCODE%'

thanks
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-12 : 05:37:32

[code]
a.branchcode like '%' + @divisionCODE + '%'
[/code]

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-12 : 05:45:49

thanks KH.

I have question. What function would process faster, a WHERE CLAUSE or INNER JOIN?

thanks.

-RON-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-12 : 05:50:04

You mean placing the condition in WHERE or INNER JOIN ?

No different in performance.

But i prefer on WHERE clause and leave table join condition at the INNER JOIN's ON clause.

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-12 : 05:53:14

Ah ok i got your your point. thanks.

-Ron-

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-12 : 12:23:47

quote:
Originally posted by khtan

You mean placing the condition in WHERE or INNER JOIN ?

No different in performance.

But i prefer on WHERE clause and leave table join condition at the INNER JOIN's ON clause.

Khtan, I don't want to split hairs on this because, in this case, that may be true. But, I just wanted to put my two cents in and say that is not always the case. In Theory, yes it shouldn't matter. But, you can gain performance by where you place a condition as well as what order you join in. Basically the old adage: in order to know which is faster you have to try it.

Cheers,

-Ryan

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-12 : 21:40:51

Hi all. I have problem again in LIKE function. How could I use LIKE in Age range?

declare @branchCODE varchar(20), @AgeRange1 int, @AgeRange2 int
set @branchCODE = '1001'
set @AgeRange1 = 20
set @AgeRange2 = 30

Select a.datehired,a.payrate,a.paytype,
b.fullname, b.id, c.description as divisiondescription,
d.description as sectiondescription, e.branchname as branchname,
f.description as whdescription, g.description as positiondescription,
p.wtaxcode,[Age] =convert(varchar(20), dbo.F_AGE_IN_YEARS( birthdate, getdate() )),
p.gender, p.civilstatus

From hremployees a, psdatacenter b, mscdivisions c,
mscsections d, mscbranches e, mscwarehouse f, mscpositions g,
pspersonaldata p

Where a.empdcno = b.dcno and a.divisioncode = c.divisioncode and
a.sectioncode = d.sectioncode and a.branchcode = e.branchcode
and a.whcode = f.whcode and a.positioncode = g.positioncode and a.empdcno = p.dcno
AND
a.branchcode like '%' + @branchCODE + '%' And +
Age like '%' + @AgeRange1 + '%' And +
Age like '%' + @AgeRange2 + '%'

And I got errors when running this.
Msg 207, Level 16, State 1, Line 44
Invalid column name 'Age'.
Msg 207, Level 16, State 1, Line 45
Invalid column name 'Age'.

Thanks.
-Ron-

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-12 : 21:58:07

Age range? There's no pattern matching there, it's just a simple condition:

WHERE Age BETWEEN @AgeRange1 AND @AgeRange2

- Jeff
http://weblogs.sqlteam.com/JeffS

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-12 : 22:00:00

you can't use column alias in the where clause. Like is more of for string

dbo.F_AGE_IN_YEARS( birthdate, getdate()) returns integer.
You can use BETWEEN or >= and <=


AND dbo.F_AGE_IN_YEARS( birthdate, getdate()) is bewteen @AgeRange1 and @AgeRange2


AND dbo.F_AGE_IN_YEARS( birthdate, getdate()) >= @AgeRange1
AND dbo.F_AGE_IN_YEARS( birthdate, getdate()) <= @AgeRange2

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-12 : 22:06:34

thanks jsmith. if possible, i need to do this in LIKE function. Im doing this because I have a front-end that passes parameters to the query.(e.g, branchCODE, AgeRange1, AgeRange2)... I have noticed in LIKE function, it does not return NULL value. For example, if AgeRange1 and AgeRange2 has no value, it will just simply skip the condition. Correct me if im wrong...but this is what i have observed.

Thanks.
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-12 : 22:10:57

"if AgeRange1 and AgeRange2 has no value"
if by NO VALUE means you are passing in NULL

then change to


AND (
       @AgeRange1 is null
    or dbo.F_AGE_IN_YEARS( birthdate, getdate()) >= @AgeRange1
    )
AND (
       @AgeRange2 is null
    or dbo.F_AGE_IN_YEARS( birthdate, getdate()) <= @AgeRange2
    )

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-12 : 22:17:23

KH sorry for my low IQ but I don't understand what your query means.

Please explain me how it works.

Thanks.
-Ron-

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-12 : 22:18:16

for best performance just do this:

set @AgeRange1 = ISNULL(@AgeRange1, 0)
set @AgeRange2 = ISNULL(@AgeRange2, 99999) -- or any sufficiently large value

(note: I strongly recommend to name these parameters @AgeMin and @AgeMax, or something like that. GIve meaning to the parameters so that they make sense).

Then, you just use the simple BETWEEN operator like I showed:

WHERE Age BETWEEN @AgeRange1 AND @AgeRange2

This will maximize any index usage as well.

- Jeff
http://weblogs.sqlteam.com/JeffS

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-12 : 22:49:36

thanks jsmith. At first i didn't understand your query(what you suppose to happen).
But now, i fully understand it. Very great!.. thanks to KH also.

-Ron-

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-13 : 06:36:21

Hi. I have problem on my query.
see what is wrong with it.

declare @branchCODE varchar(10), @divisionCODE varchar(10),
@whCODE varchar(10), @sectionCODE varchar(10),
@positionCODE varchar(10),
@payrollRATE varchar(10), @payrollFREQ varchar(10),
@Age1 int, @Age2 int, @gender varchar(10), @civilStatus varchar(10)

set @branchCODE = ''
set @divisionCODE = ''
set @whCODE = ''
set @sectionCODE = ''
set @positionCODE = ''
set @payrollRATE = ''
set @payrollFREQ = ''
set @Age1 = 0
set @Age2 = 99
set @Age1 = isnull(@Age1,0)
set @Age2 = isnull(@Age2,99999)
set @gender = ''
set @civilStatus = ''

Select a.empdcno,convert(varchar,a.datehired,103) as date,a.payrate,a.paytype,a.salrate,
b.fullname, b.id, c.description as divisiondescription,
d.description as sectiondescription, e.branchname as branchname,
f.description as whdescription, g.description as positiondescription,
p.wtaxcode,[Age] =convert(varchar(20), dbo.F_AGE_IN_YEARS( p.birthdate, getdate() )),
p.gender, p.civilstatus

From hremployees a inner join psdatacenter b on a.empdcno = b.dcno
left join mscdivisions c on a.divisioncode = c.divisioncode
left join mscsections d on a.sectioncode = d.sectioncode
left join mscbranches e on a.branchcode = e.branchcode
left join mscwarehouse f on a.whcode = f.whcode
left join mscpositions g on a.positioncode = g.positioncode
inner join pspersonaldata p on a.empdcno = p.dcno
and dbo.F_AGE_IN_YEARS( isnull(p.birthdate,getdate()), getdate()) between @Age1 and @Age2

where

a.branchcode like '%' + @branchCODE + '%' and +
a.divisioncode like '%' + @divisionCODE + '%' and +
a.whcode like '%' + @whCODE + '%' And +
a.sectioncode like '%' + @sectionCODE + '%' And +
a.positioncode like '%' + @positionCODE + '%' And +
a.payrate like '%' + @payrollRATE + '%' And +
a.paytype like '%' + @payrollFREQ + '%' and + ------------If i run until here i got exactly 69 records

p.gender like @gender + '%' and +
p.civilstatus like '%' + @civilStatus + '%' ------until here, got only 14 records....what is wrong here?

if i run --- select * from hremployees e --- i got 69 records

thanks.
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-13 : 08:13:44

what is all the + for ?


a.branchcode like '%' + @branchCODE + '%' and +
a.divisioncode like '%' + @divisionCODE + '%' and +
a.whcode like '%' + @whCODE + '%' And +
a.sectioncode like '%' + @sectionCODE + '%' And +
a.positioncode like '%' + @positionCODE + '%' And +
a.payrate like '%' + @payrollRATE + '%' And +

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-13 : 21:23:22

No difference in output even there is + or no.

thanks.
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-13 : 21:28:37

?

don't you get "Incorrect syntax near '+'." ?

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-13 : 21:40:45

no error. it is working...

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-13 : 21:50:31

declare @gender varchar(10)
set @gender = ''

select p.gender,p.birthdate,e.* from hremployees e
inner join pspersonaldata p on e.empdcno = p.dcno
where p.gender like @gender + '%'

--if i run this, i got only 14 records. It should be 69 records. what's wrong with this?

thanks.
-Ron-

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-13 : 21:58:20

O i see. I already traced the error. gender has a value of male, female, '', and NULL. It only reads the first 3 but the NULL value cannot be displayed. I think, i should convert the NULL to '' so that i can display them. How to do that?

Edit.:. PROBLEM SOLVED
isnull(a.branchcode,'') like '%' + @branchCODE + '%' and
isnull(a.divisioncode,'') like '%' + @divisionCODE + '%' and
isnull(a.whcode,'') like '%' + @whCODE + '%' And
isnull(a.sectioncode,'') like '%' + @sectionCODE + '%' And
isnull(a.positioncode,'') like '%' + @positionCODE + '%' And
isnull(a.payrate,'') like '%' + @payrollRATE + '%' And
isnull(a.paytype,'') like '%' + @payrollFREQ + '%' and

isnull(p.civilstatus,'') like '%' + @civilStatus + '%' and
isnull(p.gender,'') like @gender + '%'

thanks.
-Ron-

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources