TSQL: Converting/Casting String between Unicode and Non-Unicode

Categories: Database; Tagged with: ; @ January 16th, 2013 12:04

I got an Error in a SSIS package:

 cannot convert between unicode and non-unicode string data types.

We can use one component called ‘DataConversion’, but I think directly convert the type in SQL will be better.

SQL:

DECLARE @str_unicode NVARCHAR(32);
SET @str_unicode = NCHAR(9734);
SELECT @str_unicode

— Using CAST
SELECT CAST(@str_unicode AS VARCHAR(32))

— Using Convert
SELECT CONVERT(VARCHAR(32), @str_unicode)

Result:

image

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

Categories: Database; Tagged with: ; @ 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

Using function in T-SQL

Categories: Database; Tagged with: ; @ 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: ; @ 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



// Proudly powered by Apache, PHP, MySQL, WordPress, Bootstrap, etc,.