Like function in sql - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Like function in sql

Author

Topic

ntn104
Posting Yak Master

175 Posts

Posted - 2008-06-11 : 08:20:47

Hello,

How can I select one state (such as: PA) while the CITY_STATE field indicates both city and state. For example:

CHADDS FORD, PA
RIDGEFIELD, CT
AVON, OH

I used LIKE '%PA' Statement, but it show only 2 records, but it should be more as I checked on the table

Thank you,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 08:28:05

Try the opposite

NOT LIKE '%PA' to see if you get records back that ends with 'PA'.
That can indicate that you have hidden characters at the end of the data.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 08:28:52

Can you elaborate on this with some more sample data?
May be like this LIKE '%PA%'

ntn104
Posting Yak Master

175 Posts

Posted - 2008-06-11 : 08:36:35

quote:
Originally posted by visakh16

Can you elaborate on this with some more sample data?
May be like this LIKE '%PA%'

Yes, it work with LIKE '%PA%'

Wonderful...thanks,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 08:40:26

What?
That will also return the record

'Champaign, IL'

which is the city of Champaign, Illinois.
In your original post you wrote you wanted all cities for STATE PA, which is Pennsylvania.

E 12°55'05.25"
N 56°04'39.16"

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-06-11 : 08:51:51

wouldn't LIKE '%, PA%' OR LIKE '%,PA%' do the job?

ntn104
Posting Yak Master

175 Posts

Posted - 2008-06-11 : 09:06:03

quote:
Originally posted by Peso

What?
That will also return the record

'Champaign, IL'

which is the city of Champaign, Illinois.
In your original post you wrote you wanted all cities for STATE PA, which is Pennsylvania.

E 12°55'05.25"
N 56°04'39.16"

Yes, you are right. I want all the cities for PA...I don't know why LIKE '%PA%' work...don't know how to explain...Don't you think I miss any record. here the result:

CHADDS FORD PA
BETHLEHEM PA
DOYLESTOWN PA
DUBOIS PA
NEW HOPE PA
CHESTER SPRGS PA
NESQUEHONING PA
LANDENBERG PA
GLADWYNE PA
LAFAYETTE HL PA
LANCASTER PA
WYOMISSING PA

Thanks,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 09:11:48

As I said before, I think you have hidden characters in your data.

Run this

SELECT SUBSTRING(CITY_STATE, DATALENGTH(CITY_STATE) - 1, 2), datalength(CITY_STATE), len(CITY_STATE)
FROM Table1

and inspect the result to see if you find something unexpected.
The query above will return the two last characters in CITY_STATE column.

E 12°55'05.25"
N 56°04'39.16"

ntn104
Posting Yak Master

175 Posts

Posted - 2008-06-11 : 09:17:31

quote:
Originally posted by Peso

As I said before, I think you have hidden characters in your data.

Run this

SELECT SUBSTRING(CITY_STATE, DATALENGTH(CITY_STATE) - 1, 2), datalength(CITY_STATE), len(CITY_STATE)
FROM Table1

and inspect the result to see if you find something unexpected.

E 12°55'05.25"
N 56°04'39.16"

Error message: Anywhare procedure 'Len'not found. as I ran that...any suggestion?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 09:22:40

Are you not using Microsoft SQL Server? Are you by chance using SYBASE?

SELECT	SUBSTRING(CITY_STATE, DATALENGTH(CITY_STATE) - 1, 2) AS Last2,
	DATALENGTH(CITY_STATE) AS DL,
	LEN(CITY_STATE) AS L
FROM	Table1

E 12°55'05.25"
N 56°04'39.16"

ntn104
Posting Yak Master

175 Posts

Posted - 2008-06-11 : 09:38:06

quote:
Originally posted by Peso

Are you not using Microsoft SQL Server? Are you by chance using SYBASE?
SELECT SUBSTRING(CITY_STATE, DATALENGTH(CITY_STATE) - 1, 2) AS Last2,
DATALENGTH(CITY_STATE) AS DL,
LEN(CITY_STATE) AS L
FROM Table1

E 12°55'05.25"
N 56°04'39.16"

I am using SYSBASE ADAPTIVE SERVER.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 09:55:18

When you added the ALIAS names, did the query work then?
If not, remove the line with LEN and try again.

E 12°55'05.25"
N 56°04'39.16"

ntn104
Posting Yak Master

175 Posts

Posted - 2008-06-11 : 10:22:08

quote:
Originally posted by Peso

When you added the ALIAS names, did the query work then?
If not, remove the line with LEN and try again.

E 12°55'05.25"
N 56°04'39.16"

It does not work. I removed the LEN function, then it show how many character of the field (25 characters for all rows)....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 10:26:32

As you can see by this example, it doesn't matter if you have trailing spaces for like operator.

DECLARE	@Sample TABLE (CITY_STATE1 CHAR(25), CITY_STATE2 VARCHAR(25))

INSERT	@Sample
SELECT	'EXTON, PA      ', 'EXTON, PA'

SELECT	*
FROM	@Sample

SELECT	*
FROM	@Sample
WHERE	CITY_STATE1 LIKE '%PA'

SELECT	*
FROM	@Sample
WHERE	CITY_STATE2 LIKE '%PA'

So I guess you have other trailing characters than spaces.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 10:26:57

quote:
Originally posted by ntn104

quote:
Originally posted by Peso

When you added the ALIAS names, did the query work then?
If not, remove the line with LEN and try again.

E 12°55'05.25"
N 56°04'39.16"

It does not work. I removed the LEN function, then it show how many character of the field (25 characters for all rows)....

Will you always be having a state name at the end like PA?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 10:28:33

However, if you have "hidden" characters in your data you will get the the data you expect

DECLARE	@Sample TABLE (RowID TINYINT IDENTITY(0, 1), CITY_STATE CHAR(25))

INSERT	@Sample
SELECT	'EXTON, PA      ' UNION ALL
SELECT	'EXTON, PA' + CHAR(160) UNION ALL
SELECT	'EXTON, PA'

-- Show all records
SELECT	*
FROM	@Sample

-- Show all records ending with PA
SELECT	*
FROM	@Sample
WHERE	CITY_STATE LIKE '%PA'

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 10:30:25

[code]DECLARE @Sample TABLE (RowID TINYINT IDENTITY(0, 1), CITY_STATE CHAR(25))

INSERT @Sample
SELECT 'EXTON, PA ' UNION ALL
SELECT 'EXTON, PA' + CHAR(160) UNION ALL
SELECT 'EXTON, PA'

-- Show all records having "invalid" characters. Valid characters are a-z, space and comma.
SELECT *
FROM @Sample
WHERE CITY_STATE LIKE '%[^a-z, ]%'[/code]

E 12°55'05.25"
N 56°04'39.16"

ntn104
Posting Yak Master

175 Posts

Posted - 2008-06-11 : 10:38:53

quote:
Originally posted by visakh16

quote:
Originally posted by ntn104

quote:
Originally posted by Peso

When you added the ALIAS names, did the query work then?
If not, remove the line with LEN and try again.

E 12°55'05.25"
N 56°04'39.16"

It does not work. I removed the LEN function, then it show how many character of the field (25 characters for all rows)....

Will you always be having a state name at the end like PA?

Yes, it always lists statement at the end such as: 'RIDGEFIELD CT' OR 'CHADDSFORD PA'

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-06-11 : 11:28:29

if using sybase....you might be better off posting in a more appropriate forum (we're very MS SQL Server focussed). wwww.dbforums.com may have more assistance with the Sybase version on the SQL language.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources