Home » SQL & PL/SQL » SQL & PL/SQL » Sort for Text
Sort for Text [message #206210] Wed, 29 November 2006 03:41 Go to next message
matrik02
Messages: 64
Registered: November 2005
Member
I have field name 'IC Number' for table Employer.Data type for this field is Text.
I would like to sort the record in table employer like this 830725-12-6122, 830625-12-6122, 830525-12-6122. I like to sort this record start from 3 charactersce of this text from left.Have an idea?

[Updated on: Wed, 29 November 2006 03:56]

Report message to a moderator

Re: Sort for Text [message #206214 is a reply to message #206210] Wed, 29 November 2006 03:48 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
Data type for this field is Text.
Are you using Oracle?
What do you mean by the following?
Quote:
I like to sort this record start from 3 place of this text from right

830725-12-6122
830625-12-6122
830525-12-6122

Do you want to sort last 3 characters? (830725-12-6[122])

By
Vamsi
Re: Sort for Text [message #206222 is a reply to message #206214] Wed, 29 November 2006 03:58 Go to previous messageGo to next message
matrik02
Messages: 64
Registered: November 2005
Member
Hi

I have update the message not from right. I have change it start from left.

Sort by 2 characters . 83[07]25-12-6122 83[06]25-12-6122, 83[05]25-12-6122, 83[04]25-12-6122


[Updated on: Wed, 29 November 2006 04:01]

Report message to a moderator

Re: Sort for Text [message #206223 is a reply to message #206222] Wed, 29 November 2006 04:01 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You can use
order by with substr.

By
Vamsi
Re: Sort for Text [message #206224 is a reply to message #206214] Wed, 29 November 2006 04:02 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What 3 characters? From your post, there's 83 and 0725-12-6122. Which one of those two shows 3 characters?

But never mind that - whether it is 830 or 122 (first 3 or last 3 characters), result is unpredictable because all sample records will be the same (when you extract those "3" characters).

How to sort it? Use SUBSTR function and select whichever characters you want, such as

ORDER BY SUBSTR(column, 1, 3)
Re: Sort for Text [message #206226 is a reply to message #206224] Wed, 29 November 2006 04:03 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh, come on! You change your posts faster than Speedy Gonzales. First there were right characters, now they are left ones; there were 3 characters, now there are 2 of them. Make up your mind!
Re: Sort for Text [message #206230 is a reply to message #206226] Wed, 29 November 2006 04:11 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I think I have read his mind.....
from this....
83[07]25-12-6122

He wants to sort on the 2 characters (starting from the 3rd character). means 4th and 5th.... Cool

ORDER BY substr(<string>,3,2) <desc/asc>

By
Vamsi

[Updated on: Wed, 29 November 2006 04:14]

Report message to a moderator

Re: Sort for Text [message #206231 is a reply to message #206226] Wed, 29 November 2006 04:12 Go to previous messageGo to next message
matrik02
Messages: 64
Registered: November 2005
Member
Very sorry Guy. I not quite good in English .

The true message is i like to sort the record like this using SUBSTR

831125-12-6122
831025-12-6122
830725-12-6122
830625-12-6122
830525-12-6122

Select * from employer ORDER BY SUBSTR(NO IC, 1, 3)
Re: Sort for Text [message #206233 is a reply to message #206231] Wed, 29 November 2006 04:20 Go to previous messageGo to next message
matrik02
Messages: 64
Registered: November 2005
Member
I got it . Thank vamsi kasina and Littlefoot
Re: Sort for Text [message #206265 is a reply to message #206231] Wed, 29 November 2006 06:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you make an error in your post and people react to that, please do not update your post without telling; it makes the thread _very_ confusing!
Re: Sort for Text [message #206286 is a reply to message #206231] Wed, 29 November 2006 08:00 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
matrik02 wrote on Wed, 29 November 2006 05:12
Very sorry Guy. I not quite good in English .

The true message is i like to sort the record like this using SUBSTR

831125-12-6122
831025-12-6122
830725-12-6122
830625-12-6122
830525-12-6122

Select * from employer ORDER BY SUBSTR(NO IC, 1, 3)


This is quite wrong. It will not produce the results that are shown. Additionally, the column name is invalid.
Previous Topic: Passing CLOB to procedure in sqlplus
Next Topic: can not truncate table
Goto Forum:
  


Current Time: Mon Dec 05 06:49:14 CST 2016

Total time taken to generate the page: 0.09183 seconds