A lot of times, for scalability, for accuracy, etc., we’ll set bool or string fields to int in the database. But when we want to display user-friendly content in the application, we need to convert the results we find into user-friendly results. To achieve this goal there are several feasible methods, you can directly judge the output in accordance with the requirements of the query, or you can query the results in the program through the corresponding method to change the results after the display. This article discusses the first approach. Use SQL Server database and Access database use different statements, the following are described:

SQL Server:

Use the case when statement.

The syntax is as follows:

Select * from case database when select 1 then select 1 when select 2 then select 2...... Else End if none of the above conditions are metCopy the code

Click here or here to see what MSDN says about it.

example

select UserName,case UserRole when '0' then 'Ordinary user' when '1' then 'Administrator' end asUser rolesfrom Users
Copy the code

Truncate the string by length

select case when LEN(Title)>20  then SUBSTRING(Title,0.21) else Title end as Title from Articles
Copy the code

Access:

Use the iff() function.

The syntax is as follows:

IIf(expr, truepart, falsepart)
Copy the code

Click here to see what Microsoft Office says about it.

Expr is required. The expression whose value is to be evaluated. Truepart required. The value or expression returned when expr is True. Falsepart required. The value or expression returned when expr is False.Copy the code

If the condition is true, return the correct result; Error results are returned when the condition is not established.

Note: Although IIf returns only one truePart and FalsePart, both parts are always computed. Therefore, attention should be paid to unnecessary side effects. For example, if computing falsePart results in a zero division error, an error is generated even if expr is True.

example

select iif(IfPaid=1.'is'.'no') asWhether payment has been madefrom Accounts
Copy the code

Truncate the string by length

select iif(LEN(Title)>20.left(Title,20),Title) as Title from Articles
Copy the code

Note:

If you want to bind data to a DataGridView and the DataGridView columns have been defined, you can display the queried value by assigning the as definition of the column whose value is obtained by this method to the corresponding column’s DataPropertyName. For example, assign the Title of the above statement to the DataPropertyName of the corresponding column added by DataGridView to display the value of the first 20 characters in the original Title column of the database.