help with trim 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

SQL Server 2008 Forums

Transact-SQL (2008)

help with trim function

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2011-02-23 : 14:23:58

Hello,

I have a column in my table with a URL in each row. The data structured as below. (2 rows below)

I am trying to run a query on this table to find the MAX "car_ID"

So basically I want to trim from the left and the right of the string, so I can get this value accurately.

I would need the value between

http://www.domain.com/fyc/vdp.jsp?ct=p&car_id=

and

&dealer_id=

I'm unsure of what functions I should be using, and how to use them.. any help is greatly appreciated ! :)

http://www.domain.com/fyc/vdp.jsp?ct=p&car_id=287883691&dealer_id=65750235&car_year=2004&rdm=1283415291308&lastStartYear=1981&num_records=100&start_year=1981&body_code=0&awsp=false&search_type=used&distance=75&marketZipError=false&search_lang=en&showZipError=n&first_record=1&page_location=findacar%3A%3Aispsearchform&min_price=10000&seller_type=p&style_flag=1&sort_type=yearDESC&address=82001&end_year=2011&pager.offset=0&cardist=44&standard=false

http://www.autotrader.com/fyc/vdp.jsp?ct=p&car_id=287727866&dealer_id=65743997&car_year=1994&rdm=1283415291308&lastStartYear=1981&num_records=100&start_year=1981&body_code=0&awsp=false&search_type=used&distance=75&marketZipError=false&search_lang=en&showZipError=n&first_record=1&page_location=findacar%3A%3Aispsearchform&min_price=10000&seller_type=p&style_flag=1&sort_type=yearDESC&address=82001&end_year=2011&pager.offset=0&cardist=56&standard=false

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-23 : 14:36:40

declare @str varchar(max)

set @str = '
http://www.domain.com/fyc/vdp.jsp?ct=p&car_id=287883691&dealer_id=65750235&car_year=2004&rdm=1283415291308&lastStartYear=1981&num_records=100&start_year=1981&body_code=0&awsp=false&search_type=used&distance=75&marketZipError=false&search_lang=en&showZipError=n&first_record=1&page_location=findacar%3A%3Aispsearchform&min_price=10000&seller_type=p&style_flag=1&sort_type=yearDESC&address=82001&end_year=2011&pager.offset=0&cardist=44&standard=false

'

select substring(@str,PATINDEX('%car_id=%',@str)+7,PATINDEX('%&dealer_id=%',@str)-PATINDEX('%car_id=%',@str)-7)

Jim

Everyday I learn something that somebody else already knew

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2011-02-23 : 15:01:46

much apprecated! works perfectly :)

Thanks!
Mike

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources