Problem with the query - 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

Problem with the query

Author

Topic

john20
Starting Member

30 Posts

Posted - 2010-04-06 : 06:35:55

Hi All,

Can someone please tell me what this statement ('%'||'john'||'%') does
in the below query.

SELECT name AS "Employee Name" FROM Employees WHERE LOWER(name) LIKE LOWER ('%'||'john'||'%')

I know about '%' is use for like statement and '||' as or.

But what this ('%'||'john'||'%') whole statement means.

You answer much appriciated.

Thanks
John

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-06 : 06:45:14

In sqlserver it produces this message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '|'.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-06 : 06:45:41

Where you are using this query because this is not the valid query in sql server 2005

Vaibhav T

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 06:55:46

I don't believe this is a legal SQL statement. I tried it on 2005, and got an error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '|'.

There are 10 types of people in the world, those that understand binary, and those that don't.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-06 : 07:01:15

I think it might be a concatenation operator in some other flavour of SQL

The most logical representation in SQL SERVER is probably just


LIKE '%john%'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-06 : 07:03:55

And LOWER Function will not take effect in LIKE Operator. it will give all matching irrespective of case.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-06 : 07:07:50

<<
I think it might be a concatenation operator in some other flavour of SQL
>>

Yes it is used in ORACLE

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-06 : 07:09:57

quote:
Originally posted by john20

Hi All,

Can someone please tell me what this statement ('%'||'john'||'%') does
in the below query.

SELECT name AS "Employee Name" FROM Employees WHERE LOWER(name) LIKE LOWER ('%'||'john'||'%')

I know about '%' is use for like statement and '||' as or.

But what this ('%'||'john'||'%') whole statement means.

You answer much appriciated.

Thanks
John

|| is a concatenation operater that concatenates % with string john
If you use ORACLE post your questions at www.orafaq.com

Madhivanan

Failing to plan is Planning to fail

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 07:11:24

quote:
Originally posted by vaibhavktiwari83

And LOWER Function will not take effect in LIKE Operator. it will give all matching irrespective of case.

Isn't that what it's supposed to do in this case?

There are 10 types of people in the world, those that understand binary, and those that don't.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-06 : 07:11:26

quote:
Originally posted by vaibhavktiwari83

And LOWER Function will not take effect in LIKE Operator. it will give all matching irrespective of case.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

That's not true. Consider this:


DECLARE @foo TABLE (
	[val] VARCHAR(255) COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS
	)

INSERT @foo ([val])
          SELECT 'aaa'
UNION ALL SELECT 'AAA'

SELECT * FROM @foo

SELECT * FROM @foo WHERE [val] LIKE '%aaa%'

Because of the collation only the line aaa will be returned.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-06 : 07:19:08

Depends on what collation you use really. I think he was using a case insensitive one

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 07:22:08

quote:
Originally posted by visakh16

Depends on what collation you use really. I think he was using a case insensitive one

I'd assume case sensitive, based on the use of the LOWER() function. This would be redundant with case insensitive collation.

There are 10 types of people in the world, those that understand binary, and those that don't.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-06 : 07:22:13

Yeah i was not aware of this...
thanks for sharing
I gone through all the collation from the link below

http://msdn.microsoft.com/en-us/library/ms144250(v=SQL.90).aspx

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources