Select Replace and convert from int to string

Transact-SQL
1

here is the select from table originally.

accessright
0
1
2
3

the data i want become:

0=Deny
1=Read
2=Write
3=Admin

the records will shows like this:
accessright
Deny
Read
Write
Admin

SELECT
CASE WHEN b.RightType IN ('0') Then 'Deny';
CASE WHEN b.RightType IN ('1') Then 'Read';
CASE WHEN b.RightType IN ('2') Then 'Write';
CASE WHEN b.RightType IN ('3') Then 'Admin';
ELSE b.RightType END
from [dbo].[_EPOS_UserRoleRights] b with (nolock)

error message shows:
Conversion failed when converting the varchar value 'Deny' to data type int.

2

Don't use that. Your query will, on occasion, miss some rows and on others include some rows twice, so the data you display will, on occasion, be misleading.

You are displaying 'Deny', 'Read' etc., which are text strings, and then if the ELSE is triggered you want to display b.RightType which is an INT. That mixing of data types is causing the error to be raised.

If you convert the INT to a String then that will solve the problem

ELSE CONVERT(varchar(20), b.RightType) END

FWIW I use size = 20 for converting numbers to strings 'coz its a) easy to remember and b) big enough that the number doesn't get truncated. I use 20 even if I am only expecting a small number "just in case"!

1 Like
3

SELECT
CASE WHEN b.RightType IN ('0') Then 'Deny' else
CASE WHEN b.RightType IN ('1') Then 'Read' else
CASE WHEN b.RightType IN ('2') Then 'Write' else
CASE WHEN b.RightType IN ('3') Then 'Admin'else
CONVERT(varchar(20), b.RightType) END
from [dbo].[_EPOS_UserRoleRights] b

now the error msg is below:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'from'.

4

i get the correct one.

    SELECT 
CASE WHEN b.RightType IN ('0') Then  CONVERT(varchar(20), 'Deny') 
 WHEN b.RightType IN ('1') Then  CONVERT(varchar(20), 'Read') 
 WHEN b.RightType IN ('2') Then  CONVERT(varchar(20), 'Write') 
 WHEN b.RightType IN ('3') Then  CONVERT(varchar(20), 'Admin') else
   CONVERT(varchar(20), b.RightType) END	AS AccessRight 
 FROM  [dbo].[_EPOS_UserRoleRights] b

thanks.

5

You don't need all of that...

Select Case b.RightType
            When 0 Then 'Deny'
            When 1 Then 'Read'
            When 2 Then 'Write'
            When 3 Then 'Admin'
            Else cast(b.RightType As varchar(20))
        End As AccessRight
  From dbo.[_EPOS_UserRoleRights] b

I would not return back the RightType in the case expression - instead I would default it to 'Unknown' and include the RightType as a separate column:

Select Case b.RightType
            When 0 Then 'Deny'
            When 1 Then 'Read'
            When 2 Then 'Write'
            When 3 Then 'Admin'
            Else 'Unknown'
        End As AccessRight
     , b.RightType
  From dbo.[_EPOS_UserRoleRights] b

This way - I can see the internal value and the external value in the same results.

6

In our code: where "unknown" is a catch-all and not intended to happen "normally" we usually just concatenate the rogue value:

Select Case b.RightType
            When 0 Then 'Deny'
            When 1 Then 'Read'
            When 2 Then 'Write'
            When 3 Then 'Admin'
            Else 'Unknown [' + COALESCE(CONVERT(varchar(20), b.RightType), 'NULL') + ']'
        End As AccessRight
  From dbo.[_EPOS_UserRoleRights] b

Might not want NULL to be treated as an "Error Value" of course, depending on case-by-case circumstances.[quote="jimmy2090, post:4, topic:9811"]
CASE WHEN b.RightType IN ('0') Then CONVERT(varchar(20), 'Deny')
WHEN
[/quote]

You definitely don't need to go to the trouble of using CONVERT there ... but I can't see what triggered the "Incorrect syntax near the keyword 'from'." error - most likely the brackets didn't match up - so SQL got to the "FROM" when it was expecting a Close-Bracket, or there was one too many COMMAs and consequently it wasn't expecting FROM