sql 函数练习

 
USE[TEST] 
SELECT * FROM T_AB
GO
SELECT @@CPU_BUSY,@@CONNECTIONS,@@CURSOR_ROWS,@@DATEFIRST,@@ERROR,@@SERVERNAME,@@TOTAL_READ,@@TOTAL_WRITE

SELECT @@VERSION
SELECT ABS(-122.33)

SELECT ACOS(-100.0)

BEGIN TRY 
 --SELECT CONVERT(decimal(9,5), '123.4')
--SELECT DATEDIFF(YEAR,'1984-04-08',GETDATE()),CONVERT(INT,DATEDIFF(DAY,'1984-04-08',GETDATE())/365)
--SELECT DATEPART(MONTH,'1985-02-12'),DATENAME(MONTH,'2-16-1986')
    select ASCII('A'),UNICODE('A'),CHAR(35),NCHAR(32)
    END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
    END CATCH
    
    --select ASCII('A'),UNICODE('A'),CHAR(35),NCHAR('A')
    
    
----len function practise---------
DECLARE @MONTH VARCHAR(2)
DECLARE @DAY VARCHAR(2)
DECLARE @YEAR VARCHAR(4)
DECLARE @DATE VARCHAR(10)
BEGIN
SET @MONTH = CAST(MONTH(GETDATE()) AS VARCHAR(2))
SET @DAY = CAST(DAY(GETDATE())AS VARCHAR(2))
SET @YEAR = CAST(YEAR(GETDATE())AS VARCHAR(4))
IF LEN(@MONTH)<2
SET @MONTH = '0'+@MONTH
ELSE IF LEN(@DAY)<2
SET @DAY = '0'+@DAY

SET @DATE = @MONTH+'-'+@DAY+'-'+@YEAR
END
PRINT ERROR_MESSAGE()

SELECT @DATE
GO
-------------------
----sql split string ,LEFT RIGHT SUBSTRING REPLACE REVERSE REPLICATE() 
----SPACE()
--------------------

DECLARE @STRING VARCHAR(100)
DECLARE @INDEX INT
DECLARE @OUTPUT VARCHAR(100)
DECLARE @OUTPUT1 VARCHAR(100)
DECLARE @OUTPUT2 VARCHAR(100)
DECLARE @OUTPUT3 VARCHAR(100)
DECLARE @OUTPUT4 VARCHAR(100)
DECLARE @OUTPUT5 VARCHAR(100)
DECLARE @OUTPUT6 VARCHAR(100)
DECLARE @OUTPUT7 VARCHAR(100)
DECLARE @OUTPUT8 VARCHAR(100)
BEGIN
 
SET @STRING = 'I AM NOT A GIRL,BUT BOY'
SET @INDEX = CHARINDEX(',',@STRING)
SET @OUTPUT = LEFT(@STRING,@INDEX-1)
SET @OUTPUT1 = RIGHT(@STRING,@INDEX)
SET @OUTPUT2 = SUBSTRING(@STRING,@INDEX,4)
SET @OUTPUT3 = REPLACE(@STRING,' ','')
SET @OUTPUT6 = REVERSE(@STRING)
END
SELECT @INDEX,@INDEX+4, @OUTPUT,@OUTPUT1,@OUTPUT2,@OUTPUT3,@OUTPUT6
GO

---REPLICATE、SPACE FUNCTION  EXAMPLE  -----
USE[TEST]
IF OBJECT_ID('#T_1') IS NOT NULL
DROP TABLE #T_1
BEGIN
CREATE TABLE #T_1(FID INT ,
NAME VARCHAR(30),
ADRESS VARCHAR(100)
)
END
GO
INSERT #T_1 VALUES(1,'AA','AAAAAA')
INSERT #T_1 VALUES(2,'BB','BBBBBB')
INSERT #T_1 VALUES(3,'CC','CCCCCC')
INSERT #T_1 VALUES(4,'DD','DDDDDD')

SELECT NAME+REPLICATE('#',20-LEN(NAME)),SPACE(20-LEN(ADRESS))+ ADRESS FROM #T_1
SELECT QUOTENAME(COLUMN_NAME) AS COLUMNNAME FROM INFORMATION_SCHEMA.COLUMNS
-----------------STUFF FUNCTION PRACTISE------------
---THIS FUCTION CAN REPLACE A PART STRING OF A LONG STRING  IN OTHER STRING
SELECT STUFF('I AM 15 YEARS OLD',6,2,18)

------------------DATALENGTH()FUNCTION -----------------------
------INT categary use 4 bytes -----------------------

SELECT DATALENGTH(1),DATALENGTH(2000000000),LEN(1),LEN(222222222222222)

是不是很有意思呢~~~~