Data ordering in a Column

Get day...
I am new to SQL Server. I am building an .Net solution that uses SQL as the Backoffice. I have a Primary key column that uses char as data type. I use the following example as my key values:
1-1-1
1-1-2
1-x-x
1-1-10
The 1-1-10 row is automaticlly sorted after the 1-1-1 row. I do not desire this result. Is there a way to FORCE the sequence of my index (values) model.
Thank you in advance.
Sincerely,
Tony D. Abel
That sort order is correct - "10" follows "1" in char type fields.
If you change it, the single CHAR column you have should be broken into 3
integer columns - and all three be part of the primary key.
You can also create a user defined function to take the CHAR field you have
and break it into three pieces and then build it back together for sorting.
Lets say you break the three parts into @.P1, @.P2 and @.P3
Return the item for sorting as a varchar and build it something like
Right('000'+Cast(@.P1 as Varchar(3)),3)+Right('000'+Cast(@.P2 as
Varchar(3)),3)+Right('000'+Cast(@.P3 as Varchar(3)),3)
That would take 1-1-1 and return 001001001 and 1-1-10 and return 001001010.
They should sort fine in an ORDER by.
Steve
"Tony D. Abel" <tdabel@.sbcglobal.net> wrote in message
news:976B2BDF-9C52-4E96-B376-8495D7B92F37@.microsoft.com...
> Get day...
> I am new to SQL Server. I am building an .Net solution that uses SQL as
the Backoffice. I have a Primary key column that uses char as data type. I
use the following example as my key values:
> 1-1-1
> 1-1-2
> 1-x-x
> 1-1-10
> The 1-1-10 row is automaticlly sorted after the 1-1-1 row. I do not desire
this result. Is there a way to FORCE the sequence of my index (values)
model.
> Thank you in advance.
> Sincerely,
> Tony D. Abel
|||Good day Steve...
Thanks for your reply to my question.
I was hoping that it would be a simple solution to handle in SQL. I will manage the query now on the application side because of the quantity of data that has to be inputted for the SQL backend.
Have a great SQL day.
Sincerely,
Tony