Find char index in string after Nth occurrence SQL server

This function returns position or index of characters in a string after give number of occurrence (nth) of same character:

CREATE FUNCTION CharIndexAfterN (

      @Char VARCHAR(10)

      ,@String VARCHAR(MAX)

      ,@Occurance INT

      )

RETURNS INT

AS

BEGIN

      DECLARE @Index AS INT = 1

      WHILE @Occurance <> 0

      BEGIN

            SET @Index = CHARINDEX(@Char, @String, @Index + 1)

            SET @Occurance -= 1

      END

      RETURN @Index

END

For example:

SELECT dbo.CharIndexAfterN('ab', '***ab****ab*******ab*', 2)

Output: 10

Convert row and column delimited string to table in SQL Server

We can change row and field delimited string in tabular format. We are going to create a stored procedure which accept delimited data, row and column delimiter (comma, pipe, new line, colon etc) as a parameter and will return a result set:

CRETAE PROCEDURE DelimitedStringToTable (

      @Data AS VARCHAR(MAX)

      ,@RowDelimiter AS VARCHAR(5)

      ,@FieldDelimiter AS VARCHAR(5)

      )

AS

BEGIN

      DECLARE @DyQuery AS VARCHAR(MAX)

      SET @DyQuery = 'SELECT ''' + REPLACE(REPLACE(@data, @RowDelimiter, ''' UNION ALL SELECT '''), @FieldDelimiter, ''',''')

      IF RIGHT(@DyQuery, 19) = ' UNION ALL SELECT '''

            SET @DyQuery = LEFT(@DyQuery, LEN(@DyQuery) - 19)

      EXEC (@DyQuery)

END

To Execute:

EXECUTE DelimitedStringToTable '1|b|c|d;2|q||e;',';','|'

To Load output into a table:

DECLARE @DataTable TABLE(C1 INT,C2 VARCHAR(10),C3 VARCHAR(10),C4 VARCHAR(10))

INSERT INTO @DataTable

EXECUTE DelimitedStringToTable '1|b|c|d;2|q||e;',';','|'

SELECT * FROM @DataTable

Sample output:

C1	C2	C3	C4
1	b	c	d
2	q		e

Dynamically change database Name without using dynamic SQL in SQL Server

Sometimes there is needed to change database name of a table dynamically as passed through parameter. To do this we have to write transact SQL script as a dynamic query.   This sometimes reduced readability. Today we are going to discuss an approach to change database name or context without using dynamic SQL Query.

Let’s assume our application uses four databases:

CREATE DATABASE DB1

CREATE DATABASE DB2

CREATE DATABASE DB3

CREATE DATABASE WorkDB

Database named DB1, DB2 and DB3 has a table named Data:

USE DB1

CREATE TABLE dbo.Data(Value VARCHAR(10))

INSERT dbo.Data VALUES(DB_NAME())

GO

USE DB2

CREATE TABLE dbo.Data(Value VARCHAR(10))

INSERT dbo.Data VALUES(DB_NAME())

GO

USE DB3

CREATE TABLE dbo.Data(Value VARCHAR(10))

INSERT dbo.Data VALUES(DB_NAME())

GO

In Working Db we are going to create SYNONYM dbo.Data table which is currently pointing to DB1 database:

USE WorkDB

GO

CREATE SCHEMA DB

GO

CREATE SYNONYM DB.Data FOR DB1.dbo.Data

Now we are going to create a stored procedure which will modify the database name of synonyms:

USE WorkDB

GO

  

CREATE PROC ChangeDbNameSynonym(

      @DbName AS SYSNAME

)

AS

BEGIN

      DECLARE @DyScript AS VARCHAR(MAX)

      SET @DyScript = ''

      SELECT

            @DyScript += 'DROP SYNONYM ' +QUOTENAME(SCHEMA_NAME([schema_id])) +  '.' + QUOTENAME(name)

                  + '; CREATE SYNONYM DB.' + QUOTENAME(name)

                  + ' FOR ' + REPLACE(base_object_name, QUOTENAME(PARSENAME(base_object_name,3)) + '.',@DbName+ '.') + ';'

      FROM sys.synonyms

      WHERE [schema_id] = SCHEMA_ID('DB')

      EXEC(@DyScript)

END

Finally we are going to create procedure which accept database name as parameter and fetch data from tables in different database without using dynamic SQL:

CREATE PROC FetchData(

      @DbName AS SYSNAME

)

AS

BEGIN

      EXEC dbo.ChangeDbNameSynonym @DbName

      SELECT * FROM DB.Data

END

  

We have done!! Now we are going to verify it.

EXEC dbo.FetchData 'DB1'

EXEC dbo.FetchData 'DB2'

EXEC dbo.FetchData 'DB3'

We will get three result set as below:

Value

DB1

Value

DB2

Value

DB3

SQL Query to get list of all registered OLEDB provider in SQL Server

We can get list of all registered OLEDB provider in SQL Server by using system stored procedure:

EXEC [sys].[sp_enum_oledb_providers]

Or

EXEC sys.xp_enum_oledb_providers

Sample output:

Provider Name	Parse Name	Provider Description
SQLOLEDB	{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}	Microsoft OLE DB Provider for SQL Server
MSOLAP	{176941F9-18E8-47D6-860D-006FF2655608}	Microsoft OLE DB Provider for Analysis Services 9.0
MSDMine	{2CB6C2D3-DD7C-11D2-AFE4-00105A994724}	Microsoft OLE DB Provider For Data Mining Services
SQLNCLI11	{397C2819-8272-4532-AD3A-FB5E43BEAA39}	SQL Server Native Client 11.0
Microsoft.ACE.OLEDB.12.0	{3BE786A0-0366-4F5C-9434-25CF162E475E}	Microsoft Office 12.0 Access Database Engine OLE DB Provider
ADsDSOObject	{549365d0-ec26-11cf-8310-00aa00b505db}	OLE DB Provider for Microsoft Directory Services
SQLNCLI10	{8F4A6B68-4F36-4e3c-BE81-BC7CA4E9C45C}	SQL Server Native Client 10.0
Search.CollatorDSO	{9E175B8B-F52A-11D8-B9A5-505054503030}	Microsoft OLE DB Provider for Search
MSDASQL	{c8b522cb-5cf3-11ce-ade5-00aa0044773d}	Microsoft OLE DB Provider for ODBC Drivers
Microsoft.Jet.OLEDB.4.0	{dee35070-506b-11cf-b1aa-00aa00b8de95}	Microsoft Jet 4.0 OLE DB Provider
MSDAOSP	{dfc8bdc0-e378-11d0-9b30-0080c7e9fe95}	Microsoft OLE DB Simple Provider
MSDAORA	{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}	Microsoft OLE DB Provider for Oracle

Script to generate output in JSON format SQL Server

SQL Query to convert result set in JSON FORMAT in Sql server 2005, 2008, 2012

SELECT REPLACE('[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST((

                                                            SELECT *

                                                            FROM (

                                                                 

                                                            /*<Replace With Your SELECT Statement>*/

                                                                  ) DT

                                                            FOR XML RAW

                                                            ) AS VARCHAR(MAX)), '<row ', '{"'), '/>', '},'), '="', '":"'), '" ', '","'), '\', '\\'), '&quot;', '\"') + ']', '"},]', '"}]')

For example:

SELECT REPLACE('[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST((

                                                            SELECT *

                                                            FROM (

                                                                  SELECT *

                                                                  FROM school

                                                                  ) DT

                                                            FOR XML RAW

                                                            ) AS VARCHAR(MAX)), '<row ', '{"'), '/>', '},'), '="', '":"'), '" ', '","'), '\', '\\'), '&quot;', '\"') + ']', '"},]', '"}]')

Sample output:

[

      {"StuId":"25","StuName":"raja\"kumar","PhnNo":"k\"\"l"},

      {"StuId":"59","StuName":"ram","PhnNo":"9136784964"},

      {"StuId":"95","StuName":"hari","PhnNo":"9136784964"},

      {"StuId":"251","StuName":"raja\\kumar","PhnNo":"k\\\\l"}

]