If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > How to Sort the Numeric Data in a varchar data type field

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jul 2002
Posts: 15
Question How to Sort the Numeric Data in a varchar data type field

Hi

the data in the database is 12,41,1,2,4,6,7,13,19,20. the data type of the field is varchar

i want to sort that field by asc but the result is 1,10,11,12,13,14,15,16,17,18,19,2,20 like this it is coming.

i want it like this 1,2,3,4,5,6.... how do i do that.

Thanks in Advance

Regards
Gandhi
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Feb 2002
Posts: 2,232
You can cast or convert the varchar as integer in your order by clause.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jul 2002
Posts: 15
Hi
Thank you

if the data is only numeric it is working fine. but in the same filed i am having text data also in different condintions i have to sort the text data the time the query is giving error

Regards
Gandhi
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Feb 2002
Posts: 2,232
Give me an example the text data that is mixed with the numeric data and how the text data would impact the sorting.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jul 2002
Posts: 15
Quote:
Originally posted by rnealejr
Give me an example the text data that is mixed with the numeric data and how the text data would impact the sorting.
The Data is like this

Dept_id Filter1_Desc

PT01 1
PT01 14
PT01 2
PT01 10
DT01 Selangor
DT01 KL

my select Query is like this
SELECT *
FROM DIMENSION_MASTER
WHERE (DEPT_ID = 'pt01')
ORDER BY CAST(FILTER1_DESC AS Numeric)

The above query is working fine

SELECT *
FROM DIMENSION_MASTER
WHERE (DEPT_ID = 'Dt01')
ORDER BY CAST(FILTER1_DESC AS Numeric)

this time it is giving error i should not hard code the dept id if dept id is PT01 means one query and dept_Id is DT01 means another query the query should be common

Thanks & Regards
Gandhi
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Feb 2002
Posts: 2,232
Are there only 2 scenarios - pt01 (using numbers) and dt01 (using characters) ? And will the character scenario be sorted like a normal alpha sequence ?
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Feb 2002
Posts: 2,232
Since you want 1 generic query to handle both cases, how do you want the data displayed when you have both numbers and alphas in the same resultset ?
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jul 2002
Posts: 15
Quote:
Originally posted by rnealejr
Since you want 1 generic query to handle both cases, how do you want the data displayed when you have both numbers and alphas in the same resultset ?
presently no data is there in alpha numeric. if it is there also. it can be be any sequence no problem
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Jul 2002
Posts: 15
Quote:
Originally posted by rnealejr
Are there only 2 scenarios - pt01 (using numbers) and dt01 (using characters) ? And will the character scenario be sorted like a normal alpha sequence ?
it is not only 2 scenarios somtimes PT01(can have alpha data) dt01(can have numeric data).yes charecter can sorted by alpha sequence. i want common quey.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Feb 2002
Posts: 2,232
Are you saying that if the data is all numeric sort, it by number - if it is mixed you don't care ?

So if the following data is returned (from your example):

Dept_id Filter1_Desc

PT01 1
PT01 14
PT01 2
PT01 10
DT01 Selangor
DT01 KL

You don't care about the order of this resultset. If this is the case, how can the query be common ?
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Jul 2002
Posts: 15
Quote:
Originally posted by rnealejr
Are you saying that if the data is all numeric sort, it by number - if it is mixed you don't care ?

So if the following data is returned (from your example):

Dept_id Filter1_Desc

PT01 1
PT01 14
PT01 2
PT01 10
DT01 Selangor
DT01 KL

You don't care about the order of this resultset. If this is the case, how can the query be common ?
the data won't return in that way b'cos it is 2 different dept's one is 'PT02' and another one is DT01 i am filtering the query by dept id for that dept id the query has to return here 'dept_id' is one column and filter1_desc is another column i am seperating this one with '|'

if i filter by PT01 the data has to sort in this way

Dept_ID | Filter1_desc
PT01 | 1
PT02 | 2

if i filter by DT01 the data has to sort in this way

Dept_ID | Filter1_desc
DT01 | KL
DT02 | Selangor

Thanks & Regards
Gandhi
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Feb 2002
Posts: 2,232
How did you want to generate this list - using a stored procedure ? How are you using this data, once the data is returned ?
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: Jul 2002
Posts: 15
Quote:
Originally posted by rnealejr
How did you want to generate this list - using a stored procedure ? How are you using this data, once the data is returned ?
i want this data to be return in a Recorset. i am using this data to populate the True DB Grid. IN grid it has to be shown in the Ascending Order.

Thanks & Regards
Gandhi
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On