SQL decode sign when length concat usage details

Case 1: query Table A data, if a column (Param_value) value is too long, the foreground is not good display, only take the first 20 characters, mouse hover and then use the layer to display all values;

SQL notation: SELECTM. Param_value as Param_value,decode (sign (Length (m.param_value) -20), 1,concat (SUBSTR (m.param_value,0,20), ' ... '), M. Param_value) as Param_value_msgfrom TableA m

Introduction to the Decode () function:

Main role: Translation of query results into other values (that is, in other forms of expression, the following examples);

How to use:

Select decode (ColumnName, value 1, translation value 1, value 2, translation value 2,... Value n, translation value n, default value)

From Talbename

Where ...

Where ColumnName is the column defined in the table to select

If you are comparing with only one value

Select Monthid, decode (sale, NULL, '---', sale) sale from output

The sign () function returns 0, 1,-1, depending on whether a value is 0, positive, or negative.

The syntax for CONCAT () is as follows:

CONCAT (String 1, String 2, String 3,...): String 1, String 2, String 3, and other words concatenated together.

Please note that Oracle's concat () only allows two parameters;

In other words, you can concatenate only two strings at a time. However, in Oracle, we can use the ' | | ' To concatenate multiple strings at once.

<strong><span style= "FONT-SIZE:18PX;" >length () </span></strong> length functions are used to find the length of a string. The name of this function is not exactly the same in different repositories:

• Mysql:length ()
• Oracle:length ()
• SQL Server:len ()

Case 2: a sales business, payment method type is RMB, returns the value of the RMB field, HKD returns the value of HKD;

Select Case   b.actural_payment_currency if ' 2 ' then b.business_fee when  ' 1 ' then B.hk_business_fee end as "Amoun T_of_money "from TableB  

caseThe syntax:In the general SELECT, the syntax is as follows:

SELECT <myColumnSpec> =
Case
When <A> then <somethingA>
When <B> then <somethingB>
ELSE <somethingE>
END

this is CaseThe typical usage of, but using Casecan actually do more things. Let's say the followingGROUP byclause in the Case:

SELECT'Number of Titles',Count(*)
fromtitles
GROUP by
Case
when Price is NULL Then'unpriced'
when Price< Ten Then 'Bargain'
when PricebetweenTen and - Then 'Average'
ELSE'Gift to impress relatives'
END
GO

You can even combine these options to add aORDER byclause, as follows:

UsePubs
GO
SELECT
Case
when Price is NULL Then'unpriced'
when Price< Ten Then 'Bargain'
when PricebetweenTen and - Then 'Average'
ELSE'Gift to impress relatives'
END asRange,
Title
fromtitles
GROUP by
Case
when Price is NULL Then'unpriced'
when Price< Ten Then 'Bargain'
when PricebetweenTen and - Then 'Average'
ELSE'Gift to impress relatives'
END,
Title
ORDER by
Case
when Price is NULL Then'unpriced'
when Price< Ten Then 'Bargain'
when PricebetweenTen and - Then 'Average'
ELSE'Gift to impress relatives'
END,
Title
GO

Note that in order toGROUP byused in blocks Case, the query statement needs to beGROUP byrepeat in blockSELECTin the Block Caseblock.