Sybase database Common functions

Sybase database common functions one, string functions

1,isnull (EXP1,EXP2,EXP3,...): Returns the first non-null value, usage with coalesce (EXP1,EXP2[,EXP3 ...]) Same

2,trim (exp): remove both spaces;

3,dateformat (Date_exp,date_format): Date type to character type;

4,string (exp): converted to character type;

5,substring (EXP,INT-EXP1,[INT-EXP2]): Intercept exp starting from INT-EXP1, intercept int-exp2 characters;

6,replace (o-exp,search-exp,replace-exp): Search search-exp from O-exp, replace with Replace-exp;

7,space (INT_EXP): Returns an int space;

8,upper (exp): Converted to uppercase, equivalent to UCase (exp);

9,lower (exp): converted to lowercase letters;

10,charindex (EXP1,EXP2): Returns the location of Exp1 in the EXP2 string! Locate, Exp1 find the character, exp2 the string to be searched;

11,datalength (char_expr): Returns the length value of the character in char_expr, ignoring the trailing space;

12,right (char_expr,int_expr): Returns int_expr characters to the right of char_expr;

13,left (char_expr,int_expr): Returns int_expr characters to the left of char_expr;

14,replicate (char_expr,int_expr): repeat char_expr,int_expr times;

15,stuff (EXPR1,START,LENGTH,EXPR2): Replace the string with length of start in epxr1 with EXPR2;

16,reverse (char_expr): Anti-write char_expr in the text;

17,ltrim (char_expr): Delete head empty;

18,rtrim (char_expr): delete tail space;

19,str (Float_expr[,length[,decimal]): The conversion of numeric type to character type;

20,patindex ("%pattern%", expression): Returns the starting position of the specified style, otherwise 0;

21,nullif (EXP1,EXP1): Compares two expressions, returns a null value if equal, otherwise returns EXP1

22,number (*): return serial number, equivalent to Oracle's ROWID, but there are differences;

Second, numerical function

1,ceil (NUM-EXP): Returns the smallest integer greater than or equal to the specified expression; compatibility:iq&ase;

2,floor (NUMERIC_EXPR): Returns the largest integer less than or equal to the specified value;

3,abs (NUM-EXP): Returns the absolute value of a numeric expression; compatibility:iq&ase;

4,truncnum (1231.1251,2): Intercept the value, not rounding;

5,round (numeric_expr,int_expr): Rounding the numeric expression to the int_expr specified precision;

6,rand ([int_expr]): Returns a random floating-point number between 0-1 to specify a base value;

7,sign (INT_EXPR): Returns positive +1, 00 or minus-1;

8,sqrt (FLOAT_EXPR): Returns the square root of the specified value;

9,pi (): return constant 3.1415926;

10,power (Numeric_expr,power): Returns the value of numeric_expr to power;

11,exp (float_expr): gives the exponential value of the specified value;

Third, date function

1,day (DATE_EXP): Returns the date day value, Days (Date_exp,int): Returns the date date_exp plus int; month and months, year and years in the same vein;

2,date (exp): Converts an expression to a date and removes any hour, minute, or second; Compatibility: IQ

3,datepart (DATE-PART,DATE-EXP): Returns the corresponding value (integer) of the date component;

4,getdate (): Returns the system time;

5,datename (DATEPART,DATE_EXPR): Returns the value of the specified portion of the date_expr as a string, converted to the appropriate name;

6,datediff (DATEPART,DATE_EXPR1,DATE_EXPR2): Returns DATE_EXPR2-DATE_EXPR1, through the specified datepart metric;

7,dateadd (DATE-PART,NUM-EXP,DATE-EXP): Returns the Date-exp value generated by the specified Date-part component plus num-exp value; Compatibility: Iq&ase

8,date-part The date component represents the value:

abbreviation Value yy0001-9999QQ1-4MM1- AWK1- WuDD1- toDY1--366Dw1-7(Sunday-Saturday) HH0- atMI0- -SS0- -MS0-999

Iv. Conversion Functions

1,convert (Datetype,exp[,format-style]): Character to date type or date (exp); Compatibility: Iq&ase

Format-style value output: theYYYYMMDD -yyyy-Mm-DD Hh:nn:ssSELECT CONVERT(Date,'20101231', the),CONVERT(varchar(Ten),getdate(), -) ; --Results .- A- to  .-Geneva- -

2,cast (exp as Data-type): Returns the value of an expression converted to the provided data type; Compatibility: IQ
Date function

V. Other functions

1,rank () over (PARTITION by.. ORDER by ...) The grouping analysis function, the same order by value, returns the same order value, and partition by supports only one field or one field group (multiple fields need to be grouped by | | Spell a field (pending confirmation))

2, return the readable global ID uuidtostr (NEWID ())

3,col_length (Tab_name,col_name): Returns the defined column length; Compatibility: Iq&ase

4,length (exp): Returns the length of exp; Compatibility: IQ

Sybase database Common functions