Alias case statement

Question

I have stored procedure that sometimes return negative number in the ID. (on purpose). In additional, I have a view that shows this table (simple select), but I don't want that '-' will appear in the ID column, I mean, if the number is negative - I want to add the letter 'A' to the ID without '-'.

How can I do this?

Answer 1

SELECT REPLACE (ID, '-', 'A') MyID

Or if you were wanting to append the 'A'

SELECT REPLACE (ID, '-', '') + 'A' MyID

Answer 2

If correct I understood, you could use REPLACE in following: REPLACE(ID, '-', 'A')

So in this case will be A115 instead of -115

Also you can make It with CASE:

CASE WHEN Id < 0 THEN 'A' + CAST(Id * -1 AS NVARCHAR(20)) ELSE CAST(Id AS NVARCHAR(20)) END