SQL query to replace a string and convert rest of the string to Int

Question

I have the below query. This query is supposed to look into my accounts table with my where parameter. That will return all account codes which start with leading 3 letters "DAG". The data return is consistently in the format leading with three letters followed by a number. I then want to get the most largest number. For that I order the string by converting it to Int.

I get this below error:

Msg 207, Level 16, State 1, Line 24
Invalid column name 'AccountCodeWithoutDAG '.

Here is my SQL query.

SELECT TOP 1 
    REPLACE(AccountCode, 'DAG', '') AS AccountCodeWithoutDAG 
FROM 
    Accounts 
WHERE
    MangCode = 'ADFG'  
ORDER BY
    CONVERT(INT, AccountCodeWithoutDAG)

What am I doing wrong?

SELECT ... FROM ... WHERE ... ORDER BY ...

jarlh
– jarlh

2018-11-02 14:31:15 +00:00
Commented Nov 2, 2018 at 14:31 — jarlh
– jarlh, Commented Nov 2, 2018 at 14:31
WHERE comes after FROM

ArcherBird
– ArcherBird

2018-11-02 14:32:28 +00:00
Commented Nov 2, 2018 at 14:32 — ArcherBird
– ArcherBird, Commented Nov 2, 2018 at 14:32
sorry that was my bad copy pasting it. I edited it

HereToLearn_
– HereToLearn_

2018-11-02 14:36:48 +00:00
Commented Nov 2, 2018 at 14:36 — HereToLearn_
– HereToLearn_, Commented Nov 2, 2018 at 14:36

D-Shih · Accepted Answer · 2018-11-02 14:38:18Z

3

You can't use order by with convert alias

but you can try to use a subquery to get the AccountCodeWithoutDAG then order by it

SELECT  TOP 1 AccountCodeWithoutDAG 
FROM 
(
    SELECT REPLACE(AccountCode,'DAG','') AS AccountCodeWithoutDAG
    FROM Accounts 
    where  MangCode = 'ADFG'  
) t1
Order by  Convert(int, AccountCodeWithoutDAG )

answered Nov 2, 2018 at 14:38

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joe Phillips Over a year ago

I'm curious how this performs against just repeating the convert in the select and the orderby. Feels a lot cleaner to do it the other way

George Menoutis · Accepted Answer · 2018-11-02 14:52:05Z

2

As others have indicated, giving a calculated value an alias in the select does not give you the ability to use the same alias in subsequent clauses.

The way to do this nice-and-organized in sql server is cross apply:

SELECT TOP 1 AccountCodeWithoutDAG
FROM Accounts 
cross apply (select REPLACE(AccountCode,'DAG','') AS AccountCodeWithoutDAG ) as calcquery
where 
   MangCode = 'ADFG'  
   Order by Convert(int, AccountCodeWithoutDAG )

answered Nov 2, 2018 at 14:52

George Menoutis

7,3004 gold badges23 silver badges51 bronze badges

1 Comment

George Menoutis Over a year ago

You gotta love the cross apply! Many awesome uses!

China Syndrome · Accepted Answer · 2018-11-02 14:38:02Z

1

The problem is the AccountCodeWithoutDAG, this is an alias and cannot be used in the order Look what I did in testing below , use your replace statement in the convert part of the order by

declare @AccountCode varchar(100)='DAG123456'

SELECT  top 1 REPLACE(@AccountCode,'DAG','') AS AccountCodeWithoutDAG 
   Order by Convert(int, REPLACE(@AccountCode,'DAG',''))

answered Nov 2, 2018 at 14:38

China Syndrome

99112 silver badges24 bronze badges

2 Comments

Ajan Balakumaran Over a year ago

ORDER by runs after the SELECT statement hence the Alias is visible

Joe Phillips Over a year ago

@AjanBalakumaran Totally depends on which version of sql server he's using. What you describe is a fairly new feature

Collectives™ on Stack Overflow

SQL query to replace a string and convert rest of the string to Int

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related