String Parsing Hell

SQLServerCentral is supported by Red Gate Software Ltd.

Home Tags Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » String Parsing Hell

15 posts, Page 2 of 2

String Parsing Hell

Author

Message

mtassin

Posted Thursday, November 19, 2009 8:38 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 11:32 PM
Points: 1,573, Visits: 11,981

So far so good... except the CNXN doesn't work still.

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'CNXN' to data type int.

Originally I wrote a query to build the insert query... of course my results screen doesn't have enough room to take it. and all the good ones were dropped out. Sad

I've also got other bizarre ones like 8.X, 9.0CBT and 9.0 STM

--Mark Tassin
Proud member of the Anti-RBAR alliance.

For help with Performance click this link
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

For tips on how to post your problems
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #821668

Rob Goddard

Posted Thursday, November 19, 2009 8:54 AM

SSChasing Mays

Group: General Forum Members
Last Login: Tuesday, March 09, 2010 6:18 AM
Points: 660, Visits: 514

mtassin (11/19/2009)

So far so good... except the CNXN doesn't work still.

OK, that's my fault again. You did mention that one and I didn't add it to the test data set... How's this one looking?

select *
from #test
order by
	cast(substring(replace(replace(coalesce(left(versionval,nullif(patindex('%[,.]%',versionval),0)),versionval),'.',''),',',''),nullif(patindex('%[0-9]%',replace(replace(coalesce(left(versionval,nullif(patindex('%[,.]%',versionval),0)),versionval),'.',''),',','')),0),50) as int) desc,
	cast('.'+left(right(rtrim(versionval),len(versionval) - nullif(patindex('%[,.]%',versionval),0)),coalesce(nullif(patindex('%[^0-9]%',right(rtrim(versionval),len(versionval) - patindex('%[,.]%',versionval))),0)-1,len(right(rtrim(versionval),len(versionval) - patindex('%[,.]%',versionval))))) as money)*1000 desc,
	versionval desc

--------
I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams

Post #821694

jcrawf02

Posted Friday, November 20, 2009 6:45 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 1,060, Visits: 3,833

What is this sort order doing? Other than giving you fits?

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Post #822342

mtassin

Posted Friday, November 20, 2009 7:01 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 11:32 PM
Points: 1,573, Visits: 11,981

jcrawf02 (11/20/2009)

What is this sort order doing? Other than giving you fits?

It's going into a web based drop down combo/list box for users to select the version of software that was reported, licensed, etc.

Granted the versions are filthy... they should be managed as something consistent like version.release.build, and those should all be in a table for versions with an FK back to the table for the versions. What I get is a table in the database that predates me that was a single varchar(20) field that users could type anything into.

--Mark Tassin
Proud member of the Anti-RBAR alliance.

For help with Performance click this link
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

For tips on how to post your problems
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #822355

mtassin

Posted Friday, November 20, 2009 7:04 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 11:32 PM
Points: 1,573, Visits: 11,981

Rob Goddard (11/19/2009)

mtassin (11/19/2009)

So far so good... except the CNXN doesn't work still.

OK, that's my fault again. You did mention that one and I didn't add it to the test data set... How's this one looking?

select *
from #test
order by
	cast(substring(replace(replace(coalesce(left(versionval,nullif(patindex('%[,.]%',versionval),0)),versionval),'.',''),',',''),nullif(patindex('%[0-9]%',replace(replace(coalesce(left(versionval,nullif(patindex('%[,.]%',versionval),0)),versionval),'.',''),',','')),0),50) as int) desc,
	cast('.'+left(right(rtrim(versionval),len(versionval) - nullif(patindex('%[,.]%',versionval),0)),coalesce(nullif(patindex('%[^0-9]%',right(rtrim(versionval),len(versionval) - patindex('%[,.]%',versionval))),0)-1,len(right(rtrim(versionval),len(versionval) - patindex('%[,.]%',versionval))))) as money)*1000 desc,
	versionval desc

This works perfectly! Wow... I've used every one of those statements above, but I don't think I've ever used them all in the same field calculation.

--Mark Tassin
Proud member of the Anti-RBAR alliance.

For help with Performance click this link
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

For tips on how to post your problems
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #822360

« Prev Topic | Next Topic »

15 posts, Page 2 of 2

««1 2

Permissions