Category: T-SQL

Converting Row values into String with delimiter in SQL Server

In this post we are going to see how we can convert the Row values into comma separated String. Different SQL versions to achieve this (mentioned below), lets discuss one by one.

  1. Using Coalesce and Substring Function
  2. Using Replace Function
  3. Using Stuff Function
Using Coalesce and Substring Function

In this example, we have converted the column values (RowNO, RowValues) from RowsIntoString table into String value with comma delimiter using coalesce and substring SQL functions.

drop table if exists RowsIntoString

create table RowsIntoString (RowNo int, RowValues nvarchar(100))
insert into RowsIntoString (RowNo, RowValues) 
values (1,'first'),(2,'second'),(4,'third'),(4,'fourth'),(5,'fifth'),(7,'sixth'),(7,'seventh')
,(7,'eighth'),(9,'nineth'),(10,'tenth'),(11,'eleventh')

select * from RowsIntoString

---using Coalesce---
DECLARE @CSkeycol VARCHAR(MAX), @CSkeycol2 VARCHAR(MAX)
select @CSkeycol=COALESCE(@CSkeycol + ', ', '')+cast(RowNo as nvarchar) from RowsIntoString
select @CSkeycol as RowsIntoStringValue

select @CSkeycol2=COALESCE(@CSkeycol2 + ''', ''', '')+RowValues from RowsIntoString
select ''''+@CSkeycol2+'''' as RowsIntoStringValue

---using Substring---
declare @tmp varchar(MAX)=''
declare @tmp2 varchar(MAX)=''
select @tmp = @tmp + cast(RowNo as nvarchar) + ', ' from RowsIntoString
select SUBSTRING(@tmp, 0, LEN(@tmp)) as RowsIntoStringValue

select @tmp2 = @tmp2 + RowValues + ''', ''' from RowsIntoString
select ''''+SUBSTRING(@tmp2, 0, LEN(@tmp2)-2) as RowsIntoStringValue
Using Replace Function

In this example, we have converted the multi row string values into string value with comma delimiter using Replace SQL function.

declare @stg varchar(MAX)
set @stg='second
third
fourth
fifth
sixth
seventh'
select '''' + replace(@stg, char(10),''', ''') + '''' as RowsIntoStringValue
set @stg='1
2
3
4
5
6
7'
select replace(@stg, char(10),', ') as RowsIntoStringValue
Using Stuff Function

In this example, we have Concatenated the Rowvalues with comma which is having same RowNo (in other words, concatenating the RowValues column if RowNo column has more than one values) using Stuff SQL functions.

select * from RowsIntoString where RowNo in (4,7)

select distinct t1.RowNo,
  STUFF(
         (SELECT ', ' + RowValues
          FROM RowsIntoString t2
          where t1.RowNo = t2.RowNo
          FOR XML PATH (''))
          , 1, 1, '')  AS RowValues
from RowsIntoString t1
where RowNo in (4,7)
There is another post written by Latheesh which details another way with string_AGG.

Thanks for reading this blog post, If you enjoyed this, please share it with your friends!

Identify references of an object in SQL Server

Problem Statement:

Today, there was a requirement from one of my colleague “How to identify a function references in SQL Server?”.

Infact, He was looking for a script to identify the reference places(objects) where the function is being used in SQL Server. Please find the script below that we used to identify the references.

Script:

DECLARE @ObjectName NVARCHAR(100)
SET @ObjectName = N'split' --Give your function/proc name

--Table variable to hold the results from different databases
Declare @ResultTable Table(SourceSchema sysname, Sourceobject sysname, ReferencedDB sysname, ReferencedSchema sysname, ReferencedObject sysname)

Declare @MyQuery NVARCHAR(MAX) = N'
USE [?]
SELECT DISTINCT
	SourceSchema      = OBJECT_SCHEMA_NAME(sed.referencing_id)
	,SourceObject     = OBJECT_NAME(sed.referencing_id)
	,ReferencedDB     = ISNULL(sre.referenced_database_name, DB_NAME())
	,ReferencedSchema = ISNULL(sre.referenced_schema_name,
	OBJECT_SCHEMA_NAME(sed.referencing_id))
	,ReferencedObject = sre.referenced_entity_name
FROM sys.sql_expression_dependencies sed
CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(sed.referencing_id) + ''.'' +       	                           OBJECT_NAME(sed.referencing_id), ''OBJECT'') sre
WHERE sed.referenced_entity_name like ''%' + @ObjectName + '%'' AND sre.referenced_entity_name like ''%' + @ObjectName + '%''';

Insert into @ResultTable
EXEC sp_MSforeachdb  @MyQuery

Select * From @ResultTable

The above script uses sp_MSforeachdb to execute the query in all databases available in the SQL Server.

Please note that sp_MSforeachdb is an undocumented procedure, Microsoft may change the functionality or definition of this Stored Procedure at any time.

See Also:

You may be interested to know more on sp_MSforeachdb usage, please visit the below links

https://gallery.technet.microsoft.com/T-SQL-Script-Get-Table-Row-94e49d01
https://gallery.technet.microsoft.com/How-to-change-datacompressi-47dfce48

PARSENAME in SQL Server: a Powerful function

Introduction:
PARSENAME is one of my favourite functions in SQL Server. But, unfortunately, I could not see many people using this function efficiently.So, I thought of sharing some of its usages here with you.

PARSENAME function can be used to retrive specified part of a string that has four parts separated with DOT(.). This function can be used efficiently to retrive the each part name with four part names like fully qualified objectnames.


--Database four part Object name Usage
create Table Test_ParseName(ObjectName Varchar(100))

Insert into Test_ParseName Values('SER1.DB1.SCH1.TAB1'),('SER2.DB2.SCH2.TAB2')

Select PARSENAME(ObjectName,4) 'SERVERNAME',PARSENAME(ObjectName,3) 'DBNAME',PARSENAME(ObjectName,2) 'SCHEMANAME',PARSENAME(ObjectName,1) 'OBJNAME'
From Test_ParseName

Drop table Test_ParseName

The above is a typical example used in Books online(BOL).

Here, I would like to introduce some more scenarios where we can efficiently use PARSENAME function.

1. To separate IPAdress Sections and for its validation:
Note: IPAddress validation is not a fool-proof solution, however, used to showcase a typical usage of PARSENAME.


create Table Test_ParseName(IPAddress Varchar(100))

Insert into Test_ParseName Values('10.123.123.234'),('10.234.12.123'),('101.123.234.12.123'),('10.234.12'),('10.234.12.')

Select IPAddress,
		PARSENAME(IPAddress,4) 'SECTION1',PARSENAME(IPAddress,3) 'SECTION2',
		PARSENAME(IPAddress,2) 'SECTION3',PARSENAME(IPAddress,1) 'SECTION4',
		/*Another Usage If we want to check the test is satisfying the 4 part naming separated by "."
		Please note that it does not cover the entire validation of IPAdress*/
		Case when (PARSENAME(IPAddress,1) is not null and PARSENAME(IPAddress ,4) is not null) 
				Then 'Satisfied four part' Else 'Invalid format' End
From Test_ParseName

Drop table Test_ParseName

PARSENAME_image2

2. Another example with REPLACE function:
PARSENAME can be used along with REPLACE in many places. Try out the below;the below sample will replace “-” character with “.” and use PARSENAME to separate the four part string easily.


create Table Test_ParseName(string Varchar(100))

Insert into Test_ParseName Values('Check-function-usage-efficiently'),('Check-function-usage'),('Check-function'),('Check'),('Check-function-usage-efficiently-failed')

;With cte_Test_parsename as
(Select REPLACE(string,'-','.') stringmodified From Test_Parsename)

Select stringmodified,
		PARSENAME(stringmodified,4) 'SECTION1',PARSENAME(stringmodified,3) 'SECTION2',
		PARSENAME(stringmodified,2) 'SECTION3',PARSENAME(stringmodified,1) 'SECTION4'
From cte_Test_parsename

Drop table Test_ParseName

PARSENAME_image3

Hope you enjoyed this post. I would like to request you to share any other scenario you come across where PARSENAME can be efficiently used.

Change first character of table names with capital character including intermediate words using sp_rename

How about your collegue asking you to change all of your table names with capital character including intermediate words?

Here is a quick way for you to get it done.

Step 1: You need to create the below function. The below function would change the name that you pass to desired format. The below script is written by George Mastros (gmmastros), a mind-blowing script, may be the fastest for such scenarios.

Create FUNCTION dbo.InitialCap(@String VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
	BEGIN 
	---------------------------------------------------------
	DECLARE @Position INT

	SELECT @String   = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,
        @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)

	WHILE @Position > 0
		SELECT @String   = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,
				@Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)

	---------------------------------------------------------
	RETURN @String;
END 

Step 2: Verify the changes done by the above function. The below script will generate script to rename the tables using sp_rename . sp_rename is a system procedure to change the name of user created objects like tables, index, column or CLR user types. Do remember,when you change the object name, there is a chance of breaking your codes, so be careful!!!

select 'exec sp_rename @objname=[' + name + '], @newname=[' + dbo.InitialCap(name) + ']'
from sysObjects
where type = 'U'

Step 3: Apply the changes once verified.

Declare @s nvarchar(MAX)=''
select @s = @s+'exec sp_rename @objname=[' + name + '], @newname=[' + dbo.InitialCap(name)  +'];' 
from sysObjects
where type = 'U'
--print @s
exec(@s)

Update Table with Random numbers without duplicates in SQL Server

Problem Statement:

Recently, there was a question in one of SQL Server forum asking on updating all table rows with some Random numbers without duplicates. The person who had asked the question could achieve generating the random numbers in a way that there are some duplicates. However, his intention was updating the existing rows with unique random numbers.

Here is the script shared by Questioner


create table RandomNumberUpdate(Col1 bigint)
Go
Insert into RandomNumberUpdate Values(1)
Go 1000

Select * From RandomNumberUpdate

UPDATE RandomNumberUpdate
SET Col1 = CONVERT(int, RAND(CHECKSUM(NEWID())) * 10000)

Once after the update query executes, we can observe there are many duplicates in the table.You may try it yourself and see the duplicates. Use the below query to check the duplicates.


;With cte as
(
Select *,ROW_NUMBER()over(partition by Col1 Order by Col1 asc) Rn From RandomNumberUpdate
)Select* From cte Where rn>1

Solution:

Here is a different approach to get UNIQUE values without duplicates.


UPDATE RandomNumberUpdate
SET Col1 = Abs(Checksum(NewId()))  

Query the table to confirm for any duplicates as below and see that there are no duplicates.


;With cte as
(
Select *,ROW_NUMBER()over(partition by Col1 Order by Col1 asc) Rn From RandomNumberUpdate
)Select* From cte Where rn>1

--Clean the Code

Drop table RandomNumberUpdate