REPLACE Arguments

Hi,

I'm trying to create a view that shows columns based on user access level. I would like to be able to test for different values without having to add a REPLACE command for each value.

For example,

My UserLevelID can be 1, 2, 3, 4 or 5. 5 is an Officer level so I have the following command:

REPLACE(m.UserLevelID, 5, 'Yes'') AS 'Officer',

The problem is that I want the field to be blank for this column if the value is 1, 2, 3 or 4. I've tried the following, but none work.

REPLACE(REPLACE(m.UserLevelID, 5, 'Yes''), IN(1,2,3,4), '') AS 'Officer'

REPLACE(REPLACE(m.UserLevelID, 5, 'Yes''), NOT 5, '') AS 'Officer'

REPLACE(REPLACE(m.UserLevelID, 5, 'Yes''), < 5, '') AS 'Officer'

Any suggestions? I know I can add a REPLACE function five times, but I have a few more uses for this so I would like to find the easiest method.

Thanks,

Lee

not context or sample DDL or data but how about something like

SELECT

CASE CAST(m.UserLevelID AS varchar(10))

WHEN '5' THEN 'Officer'

ELSE ''

END

|||

If you are going to reuse it, a more compact form is something like:

substring (' Yes', 1+3*(UserLevelId/5), 3)

as in the example:

declare @.test table ( UserLevelId integer )
insert into @.test values (1)
insert into @.test values (2)
insert into @.test values (3)
insert into @.test values (4)
insert into @.test values (5)

select UserLevelId,
substring (' Yes', 1+3*(UserLevelId/5), 3) as Indicator
from @.test

-- UserLevelId Indicator
-- --
-- 1
-- 2
-- 3
-- 4
-- 5 Yes

|||

You could have a small table with the UserLevedID and the replacement values, and then JOIN against that table. If you have several such 'replacements' (or lookups as they are often called), then you could add an additional column to specify the 'group' of replacements.

SET NOCOUNT ON

CREATE TABLE MyTable
( LookupGroup int,
UserLevelID int,
UserLevel varchar(20),
)
GO

A lot better and more robust than attempting to 'hard-code' a potentially changing list of values.