CAN ANY ONE WRITE QUERY FOR BELOW DESCRIPTION!

Question

CAN ANY ONE WRITE QUERY FOR BELOW DESCRIPTION!

ansu_411

SSC Rookie

Points: 33
More actions
January 14, 2010 at 11:20 am

#215696

345 0 Test Lane 0 0, Binder, CA 00002 (MLS ID: PD1LST32)
6 N Training Road 0, Binder, CA 00006 (MLS ID: TRN1LST38)
1 N Testing Qa 0, Binder, CA 00001 (MLS ID: QA1LST267)
1 N Testing Qa 0, Binder, CA 00001 (MLS ID: QA1LST618)
1 N Testing Qa 0, Binder, CA 00001 (MLS ID: QA1LST660)
300 WILLIAMSON STREET, CELINA, TN 38551 (MLS ID: 125833)
4005 LIVINGSTON HWY., CELINA, TN 38551 (MLS ID: 127371)
1097 HORSE CREEK ROAD, CELINA, TN 38551 (MLS ID: 122846)
1022 JIMMY RENEAU ROAD, CELINA, TN 38551 (MLS ID: 506612)
4440 Neeley Creek Rd, Celina, TN 38551 (MLS ID: 865001)
130 Lake View Drive, Celina, TN 38551 (MLS ID: 129079)
1022 JIMMY RENEAU ROAD, CELINA, TN 38551 (MLS ID: 123625)
387 Morgan Hill St, Simi Valley, CA 93065 (MLS ID: 70001200)
3140 Griffon Ct, Simi Valley, CA 93065 (MLS ID: F1703822)
4877 Canoga Ave, Woodland Hills, CA 91364 (MLS ID: F1708906)
21801 SAN MIGUEL ST, Woodland Hills, CA 91364 (MLS ID: FR2056685)
4412 Canoga Ave, Woodland Hills, CA 91364 (MLS ID: F1709916)
4222 Canoga Dr, Woodland Hills, CA 91364 (MLS ID: F1710050)
4877 Canoga Avenue, Woodland Hills, CA 91364 (MLS ID: 70003126)
22300 AVENUE SAN LUIS, Woodland Hills, CA 91364 (MLS ID: FR2063889)
3404 MANDEVILLE CANYON RD, LOS ANGELES, CA 90049 (MLS ID: 06-150775)
21635 MEDINA ESTATES DR, Woodland Hills, CA 91364 (MLS ID: FR2043630)
1 N Testing Qa 123, Binder, CA 00001 - $13,998
29500 HEATHERCLIFF RD Unit: 289, Malibu, CA
1 N Testing Qa 123, Binder, CA 00001 (MLS ID: QA1LST282)
4935 DROUBAY DR, Las Vegas, NV 89122 (MLS ID: 748023)
Newbury Park, CA - $638,900
1401 Calle De Oro, Thousand Oaks, CA - $645,000
Newbury Park, CA - $648,000
856 Masterson Dr, Thousand Oaks, CA - $650,000
1841 Pinedale Ave, Lincoln, NE 68506 - $202,500
1594 Norman Ave, Thousand Oaks, CA - $650,000
7600 Ringneck Dr, Lincoln, NE 68506 - $214,500
Greenwich, CT 06830 (MLS ID: 69294)
Aspen, CO 81611 (MLS ID: 102392)
1818 SE 10Th St, Fort Lauderdale, FL 33316 (MLS ID: F751628)
Palm Beach, FL 33480 (MLS ID: 07-1241)

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Nice set of addresses. What are we supposed to do with them?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 2

it looks like the data consistently has comma-space-twoCharState in it. you'd have to verify your source.

It's my experience that MLS data is available already parsed into fields, I guess this is coming from a screen scrape off of a website?

SELECT * FROM SomeTable Where SomeColumn like '%, CA%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

I see data and no description. Please provide a description of what you are trying to accomplish with that data. Also, please provide table scripts where applicable.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lowell SSC Guru Points: 323518 More actions · Answer 4

my mind reading hat is better than yours today...

actually he put in a description along with the subject, which you can only read at the topic listing level, and cannot read in any replies or the thread itself:

CAN ANY ONE WRITE QUERY FOR BELOW DESCRIPTION!
BELOW IS THE ONE OF THE COLUMN DATA,FROM THAT WE HAVE WE HAVE TO PULL 'CA' DATA....

so it was clear he wanted stuff that had "CA" in it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Lowell (1/14/2010)
my mind reading hat is better than yours today...
actually he put in a description along with the subject, which you can only read at the topic listing level, and cannot read in any replies or the thread itself:
CAN ANY ONE WRITE QUERY FOR BELOW DESCRIPTION!
BELOW IS THE ONE OF THE COLUMN DATA,FROM THAT WE HAVE WE HAVE TO PULL 'CA' DATA....
so it was clear he wanted stuff that had "CA" in it.

Ahhh. Hmmm

Thanks Lowell.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

ansu_411,

If you'd like better help quicker with tested code in the future (although Lowell did a fine job), please read the following article...

http://www.sqlservercentral.com/articles/Best+Practices/61537/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

margarett.hance 40946 SSChasing Mays Points: 602 More actions · Answer 7

margarett.hance 40946

SSChasing Mays

Points: 602

July 29, 2013 at 8:06 am

#1636653

select * from data where Desc like '%CA%'

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

margarett.hance 40946 (7/29/2013)
select * from data where Desc like '%CA%'

The OP is now selling real estate in Argentina, 3 1/2 years later.

Margarett, if the leftmost part of the Desc column contains the state abbreviation, why use like '%CA%' instead of like 'CA%'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 9

GilaMonster (1/14/2010)
Nice set of addresses. What are we supposed to do with them?

Pay a visit to some of them. I specificaly like these two:

4935 DROUBAY DR, Las Vegas, NV 89122 (MLS ID: 748023)

Newbury Park, CA - $638,900

1401 Calle De Oro, Thousand Oaks, CA - $645,000

Newbury Park, CA - $648,000

856 Masterson Dr, Thousand Oaks, CA - $650,000

But i need to clean my guns first ...

:hehe:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 10

margrests example returns a lot of false matches, becasue'ca' appears in a large number of values int hat string. searching for comma-space-CA was really the oinly way to do it , based on what i remember of this two or three year old thread.

;With MySampleData([Desc])

AS

(

SELECT '345 0 Test Lane 0 0, Binder, CA 00002 (MLS ID: PD1LST32)' UNION ALL

SELECT '6 N Training Road 0, Binder, CA 00006 (MLS ID: TRN1LST38)' UNION ALL

SELECT '1 N Testing Qa 0, Binder, CA 00001 (MLS ID: QA1LST267)' UNION ALL

SELECT '1 N Testing Qa 0, Binder, CA 00001 (MLS ID: QA1LST618)' UNION ALL

SELECT '1 N Testing Qa 0, Binder, CA 00001 (MLS ID: QA1LST660)' UNION ALL

SELECT '300 WILLIAMSON STREET, CELINA, TN 38551 (MLS ID: 125833)' UNION ALL

SELECT '4005 LIVINGSTON HWY., CELINA, TN 38551 (MLS ID: 127371)' UNION ALL

SELECT '1097 HORSE CREEK ROAD, CELINA, TN 38551 (MLS ID: 122846)' UNION ALL

SELECT '1022 JIMMY RENEAU ROAD, CELINA, TN 38551 (MLS ID: 506612)' UNION ALL

SELECT '4440 Neeley Creek Rd, Celina, TN 38551 (MLS ID: 865001)' UNION ALL

SELECT '130 Lake View Drive, Celina, TN 38551 (MLS ID: 129079)' UNION ALL

SELECT '1022 JIMMY RENEAU ROAD, CELINA, TN 38551 (MLS ID: 123625)' UNION ALL

SELECT '387 Morgan Hill St, Simi Valley, CA 93065 (MLS ID: 70001200)' UNION ALL

SELECT '3140 Griffon Ct, Simi Valley, CA 93065 (MLS ID: F1703822)' UNION ALL

SELECT '4877 Canoga Ave, Woodland Hills, CA 91364 (MLS ID: F1708906)' UNION ALL

SELECT '21801 SAN MIGUEL ST, Woodland Hills, CA 91364 (MLS ID: FR2056685)' UNION ALL

SELECT '4412 Canoga Ave, Woodland Hills, CA 91364 (MLS ID: F1709916)' UNION ALL

SELECT '4222 Canoga Dr, Woodland Hills, CA 91364 (MLS ID: F1710050)' UNION ALL

SELECT '4877 Canoga Avenue, Woodland Hills, CA 91364 (MLS ID: 70003126)' UNION ALL

SELECT '22300 AVENUE SAN LUIS, Woodland Hills, CA 91364 (MLS ID: FR2063889)' UNION ALL

SELECT '3404 MANDEVILLE CANYON RD, LOS ANGELES, CA 90049 (MLS ID: 06-150775)' UNION ALL

SELECT '21635 MEDINA ESTATES DR, Woodland Hills, CA 91364 (MLS ID: FR2043630)' UNION ALL

SELECT '1 N Testing Qa 123, Binder, CA 00001 - $13,998' UNION ALL

SELECT '29500 HEATHERCLIFF RD Unit: 289, Malibu, CA' UNION ALL

SELECT '1 N Testing Qa 123, Binder, CA 00001 (MLS ID: QA1LST282)' UNION ALL

SELECT '4935 DROUBAY DR, Las Vegas, NV 89122 (MLS ID: 748023)' UNION ALL

SELECT 'Newbury Park, CA - $638,900' UNION ALL

SELECT '1401 Calle De Oro, Thousand Oaks, CA - $645,000' UNION ALL

SELECT 'Newbury Park, CA - $648,000' UNION ALL

SELECT '856 Masterson Dr, Thousand Oaks, CA - $650,000' UNION ALL

SELECT '1841 Pinedale Ave, Lincoln, NE 68506 - $202,500' UNION ALL

SELECT '1594 Norman Ave, Thousand Oaks, CA - $650,000' UNION ALL

SELECT '7600 Ringneck Dr, Lincoln, NE 68506 - $214,500' UNION ALL

SELECT 'Greenwich, CT 06830 (MLS ID: 69294)' UNION ALL

SELECT 'Aspen, CO 81611 (MLS ID: 102392)' UNION ALL

SELECT '1818 SE 10Th St, Fort Lauderdale, FL 33316 (MLS ID: F751628)' UNION ALL

SELECT 'Palm Beach, FL 33480 (MLS ID: 07-1241)'

)

SELECT * FROM MySampleData where [Desc] like '%CA%'

--SELECT * FROM MySampleData where [Desc] like '%, CA%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 11

ChrisM@Work (7/29/2013)
margarett.hance 40946 (7/29/2013)
select * from data where Desc like '%CA%'
The OP is now selling real estate in Argentina, 3 1/2 years later.
Margarett, if the leftmost part of the Desc column contains the state abbreviation, why use like '%CA%' instead of like 'CA%'

I can see at least one record where the string ends with CA and neither of those options would catch that. However both would pick up CA appearing elsewhere in the string so would give false positives for something like Ricarton Drive or Calgary Street if these are not in California.

Testing for '% CA %' might be better but you would need to append at least 1 blank and another character to the end first and also ensure there is also always something else present before the state abbreviation.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

crmitchell (7/30/2013)
ChrisM@Work (7/29/2013)
margarett.hance 40946 (7/29/2013)
select * from data where Desc like '%CA%'
The OP is now selling real estate in Argentina, 3 1/2 years later.
Margarett, if the leftmost part of the Desc column contains the state abbreviation, why use like '%CA%' instead of like 'CA%'
I can see at least one record where the string ends with CA and neither of those options would catch that. However both would pick up CA appearing elsewhere in the string so would give false positives for something like Ricarton Drive or Calgary Street if these are not in California.
Testing for '% CA %' might be better but you would need to append at least 1 blank and another character to the end first and also ensure there is also always something else present before the state abbreviation.

You're quite right. Failure to read the spec properly; two hours on the naughty step and no cookies.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden