Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Order by Expand / Collapse
Author
Message
Posted Monday, January 14, 2008 10:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 28, 2010 1:38 AM
Points: 3, Visits: 11
Hi,

I've got a problem with ordering my result set.

Basically I have a varchar column, the user then enters some numbers into it followed by some text. ex: "1.2.3.4 sometext" and "1.2.3.34 text"

Now the order by then sorts the result set as follows:

"1.2.3.34 text"

"1.2.3.4 sometext"



I understand why this happens. The client has told me that I can split the two fields. Having one for the numeric bit and one for the text bit.

What I would like to know is what data type would I need to use to get the numbers to sort correctly?

Or any other option for that matter.

Thanks.

Wayne
Post #442542
Posted Monday, January 14, 2008 10:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:47 PM
Points: 7,154, Visits: 15,645
The only way you will ever get that to order correctly is to either split the numbers into separate columns, or pick a width for each of the numeric sections and zero-pad the stuff. After all - "1.2.3.4" > "1.2.3.34" is going to be true with or without the extra text after it.

so you'd need to so to something like: "001.002.003.004" which would be LESS than "001.002.003.034"


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #442552
Posted Monday, January 14, 2008 10:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:47 PM
Points: 7,154, Visits: 15,645
Now - keeping in mind that you might not want to SEE the the padded version, you might be able to create a calculated field that "pads" your number sequence like I mentioned. So - the user types in "1.2.3.4" and the calculated field would then have "001.002.003.004".

You can then order by the calculated field (make it persisted and put an index on it too).


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #442554
Posted Monday, January 14, 2008 10:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 28, 2010 1:38 AM
Points: 3, Visits: 11
That's what I was thinking as well, the client however, can't give me a max size for the numbers though.
I figured I might as well just go for 4 zeros in each interval, that should be enough.
Just wanted to know if there is a better way of doing it.

Thanks
Wayne
Post #442555
Posted Monday, January 14, 2008 10:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:47 PM
Points: 7,154, Visits: 15,645
If they can't give you a width - then perhaps work on the "split into integers": meaning one field with the VarChar version, and four more to capture the octets.

Take a look at the PARSENAME function - should make that process easy.
declare @t varchar(20)
select @t='1.2.3.4'
select PARSENAME(@t,4),PARSENAME(@t,3),PARSENAME(@t,2),PARSENAME(@t,1)



----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #442561
Posted Thursday, January 21, 2010 7:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 6, 2014 3:33 PM
Points: 24, Visits: 140
You could parse the string and store the 4 integers in 4 INT columns, and use them to sort. Create a computed column that returns the 4 integers together in the W.X.Y.Z format.
Post #851223
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse