SQL

TSQL: Get the length of String including/excluding trailing spaces

Categories: Database; Tagged with: SQL • SQL Server • TSQL; @ January 16th, 2013 11:53

LEN (Transact-SQL)
Returns the number of characters of the specified string expression, excluding trailing blanks.
Note: To return the number of bytes used to represent an expression, use the DATALENGTH function.

http://msdn.microsoft.com/en-us/library/ms190329(SQL.90).aspx

DATALENGTH (Transact-SQL)

Returns the number of bytes used to represent any expression.

SQLs:

DECLARE @STR VARCHAR(32);
SET @STR = ’12 ‘
SELECT LEN(@STR) — 2
SELECT DATALENGTH(@STR); –6

Small Flex Tool for parsing column names to SQL format string

Categories: Development Notes • Flex • 分享; Tagged with: Flex • SQL • Tools; @ November 19th, 2012 18:40

Hi all,

I wrote a small Flex tool for parsing column names to SQL format, for example:

when I want to select those fields:

ColumnName

ACCOUNT

SECURITYEXCHANGE

ORDREFNO

ORDERID

BUYSELL

I need to copy those fields into my SQL editor, and put “,” for each of them, it’s boring and waste time, So I wrote a Flex tool, which can parse the fields into:

1: Added comma:

ACCOUNT,

SECURITYEXCHANGE,

ORDREFNO,

ORDERID,

BUYSELL

2: Added quotation marks and comma:

‘ACCOUNT’,

‘SECURITYEXCHANGE’,

‘ORDREFNO’,

‘ORDERID’,

‘BUYSELL’

Hope it can save your time if you are doing such kind of SQL jobs.

Get the tool: http://liguoliang.com/wp-content/uploads/2012/11/ParseStrForSQL.swf

Using function in T-SQL

Categories: Database; Tagged with: Database • SQL • TSQL; @ November 2nd, 2012 16:49

Basic function:

ALTER FUNCTION fnGetAge(@birthDate datetime) RETURNS int AS BEGIN -- RETURN MONTH(@birthDate) RETURN DateDiff(day, @birthDate, getdate())/365.25 END


SELECT dbo.fnGetAge('09/10/1985');

Table-Valued Functions:


--INLINE Table-Valued Functions, returns a result set, like a view
CREATE FUNCTION fnGetStusByScore(@score int) RETURNS TABLE
AS    
    RETURN SELECT * FROM UserDavid WHERE score = @score
SELECT * FROM fnGetStusByScore(61)

Multi-Statement Table-Valued Functions:


-- Multi-Statement Table-Valued Functions, returns a table-type result set, the table is explicitly constructed in the script. 
ALTER FUNCTION fnGetUsers(@score int)
    RETURNS @userList TABLE
        (UserID int, UserName varchar(32))
    BEGIN
        IF @score IS NULL
            BEGIN
                INSERT INTO @userList(UserID, UserName)
                    SELECT ID, userName FROM userDavid
            END
        ELSE
            BEGIN
                INSERT INTO @userList(UserID, UserName)
                    SELECT ID, userName FROM userDavid WHERE score > @score
            END
    RETURN 
    END
SELECT * FROM fnGetUsers()

TSQL: IF ELSE, For Loop

Categories: Database; Tagged with: SQL • TSQL; @ November 2nd, 2012 16:39

IF:


CREATE PROCEDURE spTestDavid
@userID int
AS
    IF EXISTS(SELECT * FROM UserDavid WHERE ID = @userID)
        PRINT 'User EXISTS: ' + CAST(@userID AS VARCHAR(32))
    ELSE 
        PRINT 'User CANNOT found' + CAST(@userID AS VARCHAR(32))

WHILE:


CREATE PROCEDURE spTestDavid2
@COUNTER int
AS
    WHILE @COUNTER > 0 
    BEGIN 
        EXEC spTestDavid @userID = @COUNTER
        SET @COUNTER = @COUNTER - 1;
    END
EXEC spTestDavid2 @COUNTER = 30

SSIS: Lookup Error: Row yielded no match during lookup

Categories: Database; Tagged with: Database • SQL • SSIS; @ October 24th, 2012 14:14

Input: row set;
Output: validate the records using the other table’s data, and output the result.
Error Mesg: Error: 0xC020901E at Data Flow Task, Lookup 1 [2409]: Row yielded no match during lookup.

In SSIS 2005, if can not find the corresponding record, the component will be failed by default.
So the Error occurred.

What we need to do is, changing the ‘Error Output’ of the lookup component, from ‘Fail Component’ to ‘Redirect row’;

Output:

Newer Posts <-> Older Posts