Various available string functions in SQL Server TIP # 63

 

There are various functions available in SQL Server and it is good to know all of them you never know when they will be helpful to you.

So lets start one by one.

1) LEN :-   By the name it is clear that LEN function give length of the parameter

For example :-

DECLARE @Name AS VARCHAR(100) = 'Rajat'
SELECT LEN(@Name)

2) LTRIM & RTRIM :-

By the name it is clear that both LTRIM  & RTRIM trim the white space from the parameter value.
LTRIM  do left trimming & RTRIM do Right side trimming.

For example :-

DECLARE @Name AS VARCHAR(100) = '     Rajat   '
SELECT LEN(@Name)  AS LengthWithSpace
SELECT LEN(RTRIM(LTRIM(@Name))) As LenghtWithLTRIMRTRIM

3) LEFT & RIGHT:-

LEFT and  RIGHT both the function provide part of the parameter according to provided length.
For example we have taken 3 characters from Left of "Rajat" which is "Raj" and with RIGHT function we got 2 letters from Right which is "AT"

For example:-

DECLARE @Name AS VARCHAR(100) = 'Rajat'

SELECT LEFT (@name,3) AS LeftFunction
SELECT RIGHT(@Name,2) As RightFunction

4) LOWER & UPPER :-

By The name it is clear that you can change case of provided string parameter.

For example
DECLARE @Name AS VARCHAR(100) = 'I am Rajat Jaiswal.'

SELECT LOWER(@Name) AS LowerCase
SELECT UPPER(@Name) AS  UpperCase

5) CONCAT :-

This is a new function introduce in latest versions. It concatenating all the provided parameters.
The best part is it handle NULL value also.

As shown in below figure we are concating FirstName, LastName, MiddleName in a new column name FullName.

and if you see below figure you will find the MiddleName have some  null values also but we did not do anything for that concate function handle it himself.

SELECT FirstName,
       LastName,
       MiddleName ,
       CONCAT(LastName,',' ,FirstName, ' ', MiddleName) As FullName 
FROM person.Person

 

I hope this might be useful for any new SQL person. There are many other functions which I will describe in  next post.

Enjoy!!!

RJ !!

Getting Comma separated value for table reference column is easy TIP #40

 

Problem :-

Most of the time we have one master table and one child table which content reference of master table. We have to fetch all the child  column as comma separated  for master table column. for example  I have a product category table and product table.  We want all the product as a comma separated in one column corresponding to each category.

Solution:-

lets understand this by an example. I am taking Adventureworks2012 database in the example.  below is default view

Now after processing we want below result

Now to achieve this we will write following command

DECLARE @CategoryProducts AS TABLE (ProductCategoryId INT,
Category VARCHAR(500),
Products VARCHAR(5000))

INSERT INTO @CategoryProducts(ProductCategoryId,Category )
SELECT pc.ProductCategoryID,pc.Name
FROM Production.ProductCategory pc

UPDATE tmp
SET Products = (SELECT p.Name + ','
                FROM  Production.Product p
                WHERE p.ProductSubcategoryID = tmp.ProductCategoryId
                ORDER BY p.Name
                FOR XML PATH (''))
FROM @CategoryProducts tmp

SELECT * FROM @CategoryProducts WHERE Products IS NOT NULL

 

So using XML path it easy to get what we want.

I hope this may help you somewhere

Enjoy!!!

RJ