SQL SERVER -- Conversion Functions-- Part-21

                   Conversion Functions are used to convert the data from one data type to another.

There are 2 types of CONVERT Functions available.

1. CAST:-  

-- CAST

-- CAST Function is used to convert the data from one data type to another data type

-- Example -1

SELECT CAST('56543.7897' AS FLOAT) AS [Float_Val],

       CAST('56543.7897' AS DECIMAL) AS [Decimal_Val],

       CAST('56543.7897' AS DECIMAL(7,2)) AS [Decimal_Value_with_length],

       CAST('01/13/2012' AS DATETIME) as [Date_Val]

 -- Example -2

DECLARE @A INT

DECLARE @B VARCHAR(100)

SET @A=100

SET @B='The Given Number is: '

--SELECT @B+@A AS TOTAL

SELECT @B+CAST(@A AS VARCHAR) AS TOTAL

2. CONVERT:-  

-- CONVERT

-- CONVERT Function is used to convert the data from one data type to another data type

-- Example -1

SELECT CONVERT(FLOAT, '56543.7897') AS [Float_Val],

       CONVERT(DECIMAL, '56543.7897') AS [Decimal_Val],

       CONVERT(DECIMAL(7,2), '56543.7897') AS [Decimal_Value_with_length],

       CONVERT(DATETIME,'01/13/2012') AS [Date_Val]

 -- Example -2

DECLARE @A INT

DECLARE @B VARCHAR(100)

SET @A=100

SET @B='The Given Number is: '

--SELECT @B+@A AS TOTAL

SELECT @B+CONVERT(VARCHAR,@A) AS TOTAL

Thanks for Looking into this.....

SQL SERVER -- String Functions-- Part-20

                   By Using String Functions we can perform following string operations.

LEN Function:-    
              Using LEN Function we can get the total number of characters in the given string.

DECLARE @STR VARCHAR(100)='Bangalore,karnataka,India'

SELECT @STR AS ActualString

SELECT LEN(@STR) as TotalLength

REVERSE Function:-   
                 Used to print the given string in the reverse order/manner.

DECLARE @STR VARCHAR(100)='Bangalore,karnataka,India'

SELECT @STR AS ActualString

SELECT REVERSE(@STR)

LTRIM Function:-    
                               Used to eliminate the unwanted spaces from the left side of the given string.

DECLARE @STR VARCHAR(100)='  Bangalore,karnataka,India'

SELECT @STR AS ActualString

SELECT LTRIM(@STR)

RTRIM Function:-    
                  Used to eliminate the unwanted spaces from the right side of the given string. 

DECLARE @STR VARCHAR(100)='Bangalore,karnataka,India'

SELECT @STR AS ActualString

SELECT RTRIM(@STR)

CHARINDEX Function:- 
                                       Used to get the position of the character from the given string.

DECLARE @STR VARCHAR(100)='Bangalore,karnataka,India'

SELECT @STR AS ActualString

SELECT CHARINDEX('K',@STR) AS POSITION

   

SUBSTRING Function:-    
                                      Used to extract the part of the given string based on the starting and ending position.

DECLARE @STR VARCHAR(100)='Bangalore,karnataka,India'

SELECT @STR AS ActualString

SELECT SUBSTRING(@STR,1,CHARINDEX(',',@STR)-1) AS CityName

ISNULL Function:-    
                               Used to replace the NULL values with user defined values.

SELECT FirstName

      ,MiddleName

         ,LastName

         ,FirstName+ MiddleName+ LastName AS FullName

         ,FirstName+ ISNULL(MiddleName,'') + LastName AS FullName_with_ISNULL

FROM DimCustomer

REPLACE Function:
                                 Used to REPLACE the part of the string using user defined values.

DECLARE @STR VARCHAR(100)='Bangalore,karnataka,India'

SELECT @STR AS ActualString

SELECT REPLACE(@STR,',','/')

COALESCE Function:
                                 Used to get first not NULL values from the given expressions.

SELECT FirstName

      ,MiddleName

         ,LastName

    ,COALESCE(MiddleName,FirstName,LastName)

FROM DimCustomer

Thanks for Looking into this.....