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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-11 : 10:10:45
|
| Tom writes "The company I work for has recently run into a item sorting problem. We sell products from multiple vendors that are in several categories. Our item numbering system works like this:VVVC###F--The first three characters (the V's) are a vendor code.--The next character (the C) is a category code.--The next three characters are numbers assigned automatically.--The last character is a letter. Recently, we reached the point where we "ran out" of numbers for our largest vendor, causing our item numbering system to number items from that vendor with four digits instead of three. In our database, the item number is a 50-character varchar field, meaning that when I display an item list sorted by item number, the database returns the items beginning with 0 first, then 1, then 2--so that item XXXX1000X is displayed between items XXXX100X and XXXX110X! I want the items to display in the correct order when I pull them from the database in my SELECT statement, but I don't know how! I've played with REPLACE and CASE, but neither of them can do it. What I need to do is to strip off the first four characters and then add a 0 to the item number if it's three digits. Is there a way for me to do this?(By the way, we're running SQL Server 7 on Windows NT 4 with Service Pack 4--but if there's a way to do it in SQL Server 2000, I'd be happy to hear it.)" |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-06-11 : 10:39:25
|
| try on these linesselect col1,col2,col3 from #testingorder by substring(acolumn,4,4)HTH-------------------------What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson |
 |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-06-11 : 11:13:39
|
| Nazim is on the right track, but I think you will need to get a bit more creative to get the sort order you are looking for... Try something along these lines: select * from mytable order by left(itemnum, 4), right(stuff(itemnum, 1, 4, '00'), 5)Edited by - izaltsman on 06/11/2002 11:16:14 |
|
|
|
caravel
Starting Member
1 Post |
Posted - 2002-06-11 : 13:03:52
|
| It has been fixed; thanks for your help. I've never seen the stuff() function before; what exactly does it do? |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-06-11 : 13:47:57
|
BOL says that STUFF...quote:
Deletes a specified length of characters and inserts another set of characters at a specified starting point
Bascally I used it to get rid of the leading characters and insert some zeros. I could accomplish the same thing with a combination of SUBSTRING, PATINDEX and string concatenation, but STUFF made things easier.Edited by - izaltsman on 06/11/2002 13:49:06 |
|
|
|
|
|
|
|
|
Large String Sorting Difficulty