sql server format function

ArticleDirectory Group aggregation using grouping Online help from SQL Server Use adventureworks; goselect salesquota, sum (salesytd) 'totalsalesytd ', grouping (salesquota) as 'grouping' from sales. salespersongroup by salesquota with rollup; go Result set inSalesquotaThe following two null values are displayed. FirstNullRepresents the null group obtained from this column in the table. S

Label:Use [DataChange] GO /****** Object: userdefinedfunction [dbo].[ F_SPLITSTR] Script date:05/09/2016 15:04:21 Apply SQL: Implement data segmentation such as data: b41j-002/145%b41j-002/15%b41j-003/36%b41j-000/00%b43k-001/00%b41j-002/045%b41j-002/055%b41j-002/21%b43k-008/00 %b81b-007/04%b43k-029/00%b43k-027/00%b43k-029/093 Take the previous digit in each set of% data together to get the data in% split B%b%b%b%b%b%b%b%b%b%b%b%b Update [ Pub_no

(Value_column) pivot_column for (columnlist)) A little explanation: Table_source: is the table we want to convert. pivot_column: is the name of the column to be row-column. Value_column: is the value of the converted column. Columnlist is the column to be generated. Also the above example, using pivot can write the same result: Select Studentname, [Chinese] Chinese, [ math] Math, [English] 中文版 from (SELECT * from @t) T1 Pivot (SUM (Score) for Subject in ([Chinese],[Engli

Label:--视图、存储过程、函数名称DECLARE @NAME NVARCHAR(255);--局部游标DECLARE @CUR CURSOR--自动修改未上状态为旷课SET @CUR=CURSOR SCROLL DYNAMIC FORSELECT NAME FROM DBO.SYSOBJECTS WHERE NAME NOT IN (‘SYSCONSTRAINTS‘,‘SYSSEGMENTS‘)AND(OBJECTPROPERTY(ID, N‘IsView‘) = 1 --视图OR OBJECTPROPERTY(ID,N‘IsProcedure‘) = 1 --存储过程OR OBJECTPROPERTY(ID,N‘IsScalarFunction‘) = 1 --标量函数OR OBJECTPROPERTY(ID,N‘IsTableFunction‘) = 1 --标题函数OR OBJECTPROPERTY(ID,N‘IsInlineFunction‘) = 1 --内联函数);OPEN @CUR;FETCH NEXT FROM @CUR INTO @NAMEWHILE (@@FE

Tags: serve function--tween Select CTI Functions Col GPOCreate function [dbo]. [FUN_GETBH] (@char nchar (2))returns intAsBeginReturncase where Unicode (@char) between 19968 and 40869 then (Select top 1 ID from (Select Id=1,ch=n ' sanctioned 'UNION ALL select 2,n ' 阝 'UNION ALL select 3,n ' horse 'UNION ALL select 4,n ' Wind 'UNION ALL select 5,n ' Dragon 'UNION ALL select 6,n ' Qi 'UNION ALL select 7,n ' Tu

CREATE FUNCTION [dbo]. [Fn_topgetorgbyusername] (@UserName NVARCHAR (128)) RETURNS @showOrg table (id NVARCHAR) asbegindeclare @baseOrg table (ID NVARCHAR ($), FullPath NVARCHAR ($), shortname NVARCHAR (+), ParentID NVARCHAR (+), [level] int,index_baseorg INT) INSERT into @baseOrgSELECT id,fullpath,shortname, Parentid,[level],row_number () over (ORDER by id) from Sysorganization WHERE ID in (SELECT OrgID from Sysposition, Sysuserpo Sition,sysuserwher

The merge keyword is a magical DML keyword. It was introduced in SQL Server 2008, and it can be insert,update,delete simple and a sentence. MSDN's explanation for the merge is very concise: "Inserts, updates, or deletes are performed on the target table based on the results of joins to the source table." MERGE target Table USING source Table On matching Criteria When matched THEN Statement When not ma

Label:Definition and usageThe DATEPART () function is used to return a separate part of a date/time, such as year, month, day, hour, minute, and so on.GrammarDATEPART (Datepart,date) The date parameter is a valid day expression. The datepart parameter can be the following value: DatePart Abbreviations Years YY, yyyy Quarter QQ, Q Month MM, M The day of th

logarithmS:select log (2.7182818284590451) value 1O:select ln (2.7182818284590451) value from dual; 1 8. Take 10 as the base logarithmS:select log10 (a) value 1O:select log (10,10) value from dual; 1 9. Take the SquareS:select SQUARE (4) value 16O:select Power (4,2) value from dual 16 10. Take the square rootS:select SQRT (4) Value 2O:select SQRT (4) value from dual 2 11. To find any number of the bottom of the powerS:select Power (3,4) value 81O:select Power (3,4) value from dual 81 12. T

SQL Server writes a function to get Chinese pinyin code (first letter) Create function fun_getpy (@str nvarchar (4000)) Returns nvarchar (4000) As Begin DECLARE @word nchar (1), @PY nvarchar (4000) Set @PY = ' While Len (@str) >0 Begin Set @word =left (@str, 1) --If non-Chinese characters, return the original

(M,0,getdate ()) +1, 0))--. this MondaySELECT DATEADD (wk, DATEDIFF (Wk,0,getdate ()), 0) --. this SundaySelect DATEADD (Wk,datediff (Wk,0,getdate ()), 6) --. first day of the yearSELECT DATEADD (yy, DATEDIFF (Yy,0,getdate ()), 0) --. the last day of the yearSELECT DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()) +1, 0))--. the first day of the quarterSELECT DATEADD (QQ, DATEDIFF (Qq,0,getdate ()), 0) --. the night of the daySELECT DATEADD (DD, DATEDIFF (Dd,0,getdate ()), 0) --. last day

Label:When the project was recently expanded, the problem was to convert the integral type to a specified length of 16 binaryAt first it was the direct use of cast (varbinary (4)) but finding out this doesn't solve my problem.So I searched the Internet, and then changed, the following is the function:1 Create FunctionInttohexlength (@Num int,@HxLength int)2 returns varchar( -)3 as4 begin5 Declare @Mods int,@res varchar( -),@Length int6 Set @res="

Tags: style blog io ar color sp on data divLEN (' T ') =1LEN (' T ') =2Be aware of the decomposition of strings in the database, such as ' ^ ' separating ' X ^ t ', to be aware that the last ' t ' is broken down to ' t 'The following code can be used to complete the decompositionSETAnsi_nulls onGOSETQuoted_identifier onGOCREATE function [dbo].[Fnsplitwithempty](@SourceStr varchar(8000),@StrSeprate varchar(Ten))returns @temp Table(IDint, strlistvarchar

Label:This is a common face test, which is often used in practical projects. Requirements: To find product categories for the group, the most expensive product information in each group. The implementation process is as follows: declare @t table (ProductID int, ProductName varchar), ProductType varchar (20 ), price int)--Test data Insert @t Select 1, ' name1 ', ' P1 ', 3 union ALL Select 2, ' name2 ', ' P1 ', 5 union ALL Select 3, ' Name3 ', ' P2 ', 4 union ALL Select 4, ' Name4 ', ' P2 ', 4--p

-- SQL Server itself also has a function with an encrypted password: -- pwdencrypt, pwdcompare Declare @ Logintable Table (Username Varchar (20), passwd varbinary (256) insert @ logintable (username, passwd) Values ( 'Yourname' , Pwdencrypt ( '123' )) Declare @ PWD varbinary (256) Select @ Pwd = passwd From @ Logintable Where Username = 'Yourname' -- Comparison:

(varchar), GETDATE (), 25): 2006-05-16 10:57:47.250Select CONVERT (varchar), GETDATE (), (+): 2006 10:57amSelect CONVERT (varchar), GETDATE (), 101): 05/16/2006Select CONVERT (varchar), GETDATE (), 102): 2006.05.16Select CONVERT (varchar), GETDATE (), 103): 16/05/2006Select CONVERT (varchar), GETDATE (), 104): 16.05.2006Select CONVERT (varchar), GETDATE (), 105): 16-05-2006Select CONVERT (varchar), GETDATE (), 106): 16 05 2006Select CONVERT (varchar), GETDATE (), 107): 05 16, 2006Select CONVERT

Tags: Ace type CTE--data type TED expression string table binaryREPLACEReplaces all occurrences of the second given string expression in the first string expression with a third expression.GrammarREPLACE (' String_replace1 ', ' string_replace2 ', ' string_replace3 ')Parameters"' String_replace1 'The string expression to search for. String_replace1 can be either character data or binary data."' String_replace2 'The string expression to be found. String_replace2 can be either character data or bin

Label:REPLACEReplaces all occurrences of the second given string expression in the first string expression with a third expression.GrammarREPLACE (' String_replace1 ', ' string_replace2 ', ' string_replace3 ')Parameters"' String_replace1 'The string expression to search for. String_replace1 can be either character data or binary data."' String_replace2 'The string expression to be found. String_replace2 can be either character data or binary data."' String_replace3 'The replacement string expres

(calendar.month) +calendar.get (calendar.year) *12;//month = year of the current year *12 + month of the current year, same as - Calendar.settime (endDate); - intEndmonth = Calendar.get (calendar.month) +calendar.get (calendar.year) *12; - returnEndmonth-Startmonth; - } in /*find the number of days between StartDate and EndDate*/ - if("D". Equalsignorecase (datetype) | | "DD". Equalsignorecase (Datetype)) { to LongStartTime =startdate.gettime (); + LongEndTime =enddate.gettime (); - return(int)

GrammarCONVERT (data_type (length),data_to_be_converted,style)data_type (length) Specifies the target data type (with optional lengths). The data_to_be_converted contains values that need to be converted. style Specifies the date/time output format.The style value that can be used:Find out how many of them are better to show:Statement: Select convert (nvarchar), GETDATE (), 20) or select convert (nvarchar (+), GETDATE (), 120)Result: 2015-09-22 01:35:33Statement: Select convert (nvarchar), GETDA