BEGINNER'S QUESTION: SORTING IN ALPHABETICAL ORDER

Hi

I am trying to convert an Access back end into a SQL Server back end and cannot work out how to convert a query that sorts addresses that start with numbers into alphabetical order.

In Access I defined a function that converted the full address to one without the initial numbers eg:
66 Acacia Gardens to Acacia Gardens

I then used this function as an expression in the field column of the query and set it to sort by this field. This put the rows into alphabetical order. (A second function was used to return just the number which was used as the second sort).

I am converting the Access query into a stored procedure. I have successfully converted the Access number removal function into a separate stored procedure and I was intending to execute it from within the main stored procedure but I cannot work out how to do this. I don't think you can insert an Execute statement into a field as you can with a function in Access.

Code for working number removal stored procedure:

ALTER PROCEDURE spRemoveInitialNumbers
@.JobName varchar(20), @.FirstNumbersRemoved varchar(20) OUTPUT
As
DECLARE @.Chopped varchar(20)
DECLARE @.CharacterCount tinyint
DECLARE @.CharacterCode tinyint
SET @.Chopped = @.JobName
IF Substring(@.Chopped,1,5) = 'Unit '
Begin
SET @.Chopped = Right(@.Chopped, Len(@.Chopped)-5)
End
ELSE
SET @.CharacterCount = 1
WHILE @.CharacterCount<=Len(@.Chopped)
BEGIN
SET @.CharacterCode=Ascii(Substring(@.Chopped,@.CharacterCount,1))
If @.CharacterCode>64 and @.CharacterCode<91
Begin
SET @.Chopped=Right(@.Chopped, Len(@.Chopped) - @.CharacterCount + 1)
Break
End
Else
Set @.CharacterCount = @.CharacterCount + 1
End
SET @.FirstNumbersRemoved=@.Chopped

Code for non-working main stored procedure:

SELECT dbo.tblJobs.JobID, dbo.tblJobs.JobNumber, dbo.tblJobs.Surveyor, dbo.tblClients.ClientName, dbo.tblJobs.JobName, dbo.tblJobs.Description,
dbo.tblJobs.Status, dbo.tblJobs.UrgencyCode, dbo.tblJobs.EstimatedFeesOutstanding, dbo.tblJobs.[Live/Dead],
[Intended to Execute SpRemoveInitialNumbers here] AS Expr1,
SUM(dbo.tblFeeAccounts.TotalLessVATandExpenses - dbo.tblFeeAccounts.SubContactors) AS TotalNet, dbo.tblJobs.InstructionDate
FROM dbo.tblJobs INNER JOIN
dbo.tblClients ON dbo.tblJobs.ClientID = dbo.tblClients.ClientID LEFT OUTER JOIN
dbo.tblFeeAccounts ON dbo.tblJobs.JobID = dbo.tblFeeAccounts.JobID
GROUP BY dbo.tblJobs.JobNumber, dbo.tblJobs.Surveyor, dbo.tblClients.ClientName, dbo.tblJobs.JobName, dbo.tblJobs.Description, dbo.tblJobs.Status,
dbo.tblJobs.UrgencyCode, dbo.tblJobs.EstimatedFeesOutstanding, dbo.tblJobs.[Live/Dead], dbo.tblJobs.InstructionDate, dbo.tblJobs.JobID
HAVING (dbo.tblJobs.[Live/Dead] = N'D')
ORDER BY dbo.tblClients.ClientName, dbo.tblJobs.JobName

I would like to undertake the sort on the server, not client side hence my attempt to use stored procedures. I'm not sure that I am going about this the right way. Can anyone help me?

Many thanks.

Whitebeam

Please put the logic of SpRemoveInitialNumbers SP into a TSQL scalar user-defined function. You can then call it from the SELECT list. Alternatively, you can also embed the required substring / charindex / patindex expression in the SELECT list itself for better performance. ( I am assuming you use such functions to remove the initial numbers).