REPLACE Arguments

lvance1611a

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

lkh

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

Kent Waldrop 2007 Mar

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

Arnie Rowland

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.