How to concatenate values from previous row to current row in SQL?
Answer:
DECLARE @t TABLE (
id INT IDENTITY(1,1) PRIMARY key,
drink_name VARCHAR(30)
)
INSERT INTO @t (drink_name)
SELECT * FROM (VALUES ('milk tea'),('lemon tea'),('milk shake')) x(drink_name)
SELECT * FROM @t
SELECT id,
STUFF( (SELECT ', ' + drink_name
FROM @t t2
WHERE t2.id <= t1.id
FOR XML PATH('')),1,2,'') AS drink_name
FROM @t t1
GO
Source
How to check the database recovery progress in SQL Server?
-- check database recovery progress
DECLARE @database_name VARCHAR(64) = 'your_database_name' --change
DECLARE @pre_text AS nvarchar(100) = '(approximately '
DECLARE @post_text AS nvarchar(100) = ' seconds remain'
DECLARE @error_log AS TABLE (
log_date DATETIME2(0),
proess_detail VARCHAR(64),
full_text VARCHAR(MAX)
)
INSERT INTO @error_log
EXEC master..sp_readerrorlog 0, 1, 'Recovery of database', @database_name
INSERT INTO @error_log
EXEC master..sp_readerrorlog 0, 1, 'Recovery completed', @database_name
SELECT TOP 1
log_date,
@database_name AS database_name,
CASE WHEN SUBSTRING(full_text,1,18) = 'Recovery completed' THEN '100'
ELSE SUBSTRING(full_text, CHARINDEX(') is ', full_text) + LEN(') is '), CHARINDEX('% complete', full_text) - (CHARINDEX(') is ', full_text) + LEN(') is ')))
END AS percent_complete,
CASE WHEN SUBSTRING(full_text,1,18) = 'Recovery completed' THEN 0
ELSE SUBSTRING(full_text, CHARINDEX(@pre_text, full_text) + LEN(@pre_text), CHARINDEX(@post_text, full_text) - (CHARINDEX(@pre_text, full_text) + LEN(@pre_text)))
END AS seconds_remaining,
CASE WHEN SUBSTRING(full_text,1,18) = 'Recovery completed' THEN 0
ELSE CAST(CAST(SUBSTRING(full_text, CHARINDEX(@pre_text, full_text) + LEN(@pre_text), CHARINDEX(@post_text, full_text) - (CHARINDEX(@pre_text, full_text) + LEN(@pre_text))) AS DECIMAL)/60 AS DECIMAL(18,2))
END AS minutes_remaining,
CASE WHEN SUBSTRING(full_text,1,18) = 'Recovery completed' THEN 0
ELSE CAST(CAST(SUBSTRING(full_text, CHARINDEX(@pre_text, full_text) + LEN(@pre_text), CHARINDEX(@post_text, full_text) - (CHARINDEX(@pre_text, full_text) + LEN(@pre_text))) AS DECIMAL)/3600 AS DECIMAL(18,2))
END AS hours_remaining,
full_text
FROM @error_log
ORDER BY log_date desc
How to identify similarly pronounced words in SQL server?
There are two functions in SQL Server that are used to identify whether the two strings are pronounced similarly or not.
They are
-
SOUNDEX()
- This function takes a string as parameter and returns a four-character code. This code is called as Soundex. When this code is calculated it basically ignores the vowels (A, E, I, O, U), H, W, and Y unless they are the first letter of the string.
-
DIFFERENCE()
- This function takes two strings as parameter and returns a integer value from 1 to 4. This function internally calculates the SOUNDEX code for each of the string and find the difference between the two SOUNDEX
code.
SELECT
SOUNDEX ('SQL') AS SQL,
SOUNDEX ('Sequel') AS Sequel,
DIFFERENCE('SQL', 'Sequel') AS Similarity;
SELECT
SOUNDEX ('Michael Jackson') AS Michael_Jackson,
SOUNDEX ('Mitchel Johnson') AS Mitchel_Johnson,
DIFFERENCE('Michael Jackson','Mitchel Johnson') AS Similarity;
SELECT
SOUNDEX ('Ramesh') AS Ramesh,
SOUNDEX ('Suresh') AS Suresh,
DIFFERENCE('Ramesh','Suresh') AS Similarity;
SELECT
SOUNDEX ('Tamil') AS Tamil,
SOUNDEX ('Malayalam') AS Malayalam,
DIFFERENCE('Tamil','Malayalam') AS Similarity;
The output of the DIFFERENCE
function
Output | Meaning |
---|
1 | Not similar |
2 | Very less similar |
3 | Some what similar |
4 | Exact match/ Mostly similar |
If you like this question, you may also like these…
How to concatenate multiple rows into a single string in SQL?
Write a SQL query to concatenate multiple rows into a single string?
USE TestDB;
GO
DROP TABLE IF EXISTS dbo.product_category;
GO
CREATE TABLE dbo.product_category (
product_category_id INT NOT NULL PRIMARY KEY,
product_category_name VARCHAR(50)
)
GO
INSERT INTO dbo.product_category VALUES (1,'Bikes');
INSERT INTO dbo.product_category VALUES (2,'Components');
INSERT INTO dbo.product_category VALUES (3,NULL);
INSERT INTO dbo.product_category VALUES (4,'Accessories');
INSERT INTO dbo.product_category VALUES (5,'Clothing');
GO
SELECT * FROM dbo.product_category;
Solution
This can be implemented in different ways.
-
STUFF function with XML path
We are concatenating comma with the product category name and then converting the products as single string using xml path. Then replace the leading comma with blank using stuff
function.
-
Substring function with XML path
This is similar to the previous appraoch. But instead of replacing the leading comma, we are you using substring
function to select all the text except the leading comma. We have given approximate length as 10000 and this can be modified based on the scenario.
-
STRING_AGG function
This string_agg
function was introduced in SQL Server 2017. It concatenates the values of string and places separator(comma in our case) values between them. The separator isn’t added at the end of string. If there are NULL, then it will be ignored and separator will not be added for that.
-
COALESCE function
This is an old way of concatenating the rows into a string. If there is a NULL, the output will NULL as well. We should either filter out the NULL or replace a NULL with other string value. In the solution below we have replaced with a blank value and this is the reason there is two commas in the result.
-- #1 using stuff function and xml path
SELECT STUFF(
(SELECT ',' + product_category_name
FROM dbo.product_category
FOR XML PATH ('')),1,1,'')
-- #2 using substring function and xml path
SELECT SUBSTRING(
(SELECT ',' + product_category_name
FROM dbo.product_category
FOR XML PATH ('')),2,10000)
-- #3 using string_agg function
SELECT STRING_AGG(product_category_name,',')
FROM dbo.product_category
-- #4 using coalesce when there is null value
DECLARE @concat_string VARCHAR(MAX);
SELECT @concat_string = COALESCE(@concat_string+',','') + COALESCE(product_category_name,'')
FROM dbo.product_category
SELECT @concat_string;
GO
-- #4 using coalesce when there is not any null value
DECLARE @concat_string VARCHAR(MAX);
SELECT @concat_string = COALESCE(@concat_string+',','') + product_category_name
FROM dbo.product_category
WHERE product_category_name IS NOT NULL
SELECT @concat_string;
GO
Output:
How to get any object’s definition in SQL Server?
You can use the below query to get the definition of any object in SQL Server. You can replace the object type in the query to get the definition of the object.
USE AdventureWorks2019;
GO
SELECT object_name(object_id) as stored_procedure_name,
definition
FROM sys.sql_modules
WHERE object_name(object_id) IN (select name from sys.procedures);
SELECT object_name(object_id) as view_name,
definition
FROM sys.sql_modules
WHERE object_name(object_id) IN (select name from sys.views);
SELECT object_name(object_id) as trigger_name,
definition
FROM sys.sql_modules
WHERE object_name(object_id) IN (select name from sys.triggers);
SELECT object_name(object_id) as function_name,
definition
FROM sys.sql_modules
WHERE object_name(object_id) IN
(select name from sys.objects where type IN ('FN','IF','TF'));
Sample Output
How to find a SQL Server agent jobs last executed and next scheduled date time?
You can execute the below query to get the list of SQL Server agents jobs and when it was last executed and time it took to complete the job and when it is scheduled to run next.
USE msdb;
-- List out all the SQL agent job in that server with details about when it was last executed,last run duration and when it is scheduled to run next.
;WITH cte_1 AS (
SELECT
sj.name AS job_name,
sj.description AS job_description,
sj.enabled,
sh.step_name,
TRY_CAST(CAST(js.next_run_date AS NVARCHAR(20)) AS DATE) next_run_date,
STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(js.next_run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') next_run_time,
TRY_CAST(CAST(sh.run_date AS NVARCHAR(20)) AS DATE) last_run_date,
STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(sh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') last_run_time,
CASE WHEN sh.run_duration > 235959 THEN
CAST((CAST(LEFT(CAST(sh.run_duration AS VARCHAR), LEN(CAST(sh.run_duration AS VARCHAR)) - 4) AS INT) / 24) AS VARCHAR)
+ '.' + RIGHT('00' + CAST(CAST(LEFT(CAST(sh.run_duration AS VARCHAR), LEN(CAST(sh.run_duration AS VARCHAR)) - 4) AS INT) % 24 AS VARCHAR), 2)
+ ':' + STUFF(CAST(RIGHT(CAST(sh.run_duration AS VARCHAR), 4) AS VARCHAR(6)), 3, 0, ':')
ELSE STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(sh.run_duration AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
END AS last_run_duration
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobhistory sh ON sj.job_id = sh.job_id
LEFT JOIN msdb.dbo.sysjobschedules AS js ON sj.job_id = js.job_id
WHERE sh.step_name = '(Job outcome)'
), cte_2 AS (
SELECT
job_name,
job_description,
enabled,
DATEADD(ms, DATEDIFF(ms, '00:00:00', last_run_time ), CONVERT(DATETIME, last_run_date)) AS last_run_datetime,
last_run_duration,
DATEADD(ms, DATEDIFF(ms, '00:00:00', next_run_time ), CONVERT(DATETIME, next_run_date)) AS next_run_datetime
FROM cte_1
), cte_3 AS (
SELECT
job_name,
job_description,
enabled,
last_run_datetime,
last_run_duration,
next_run_datetime,
rownum = DENSE_RANK() OVER(PARTITION BY job_name ORDER BY last_run_datetime DESC)
from cte_2
)
SELECT
job_name,
job_description,
enabled,
last_run_datetime,
last_run_duration,
next_run_datetime
FROM cte_3
WHERE rownum = 1
ORDER BY last_run_datetime DESC,
job_name ASC
GO
Output:
List of all foreign keys and referenced tables and columns
-- to get list of foreign keys and related tables and columns.
SELECT fk.name AS foreign_key_name,
pt.name AS parent_table_name,
pc.name AS parent_column_name,
ct.name AS referenced_table_name,
cc.name AS referenced_column_name
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN sys.tables pt ON pt.object_id = fk.parent_object_id
INNER JOIN sys.columns pc ON fkc.parent_column_id = pc.column_id AND pc.object_id = pt.object_id
INNER JOIN sys.tables ct ON ct.object_id = fk.referenced_object_id
INNER JOIN sys.columns cc ON cc.object_id = ct.object_id AND fkc.referenced_column_id = cc.column_id
ORDER BY pt.name, pc.name;
Sample Output
List out all the indexes defined on a table and column details.
USE test_db;
GO
SELECT
OBJECT_NAME(i.object_id) AS table_name
,i.type_desc AS index_type
,i.name AS index_name
,COL_NAME(ic.object_id,ic.column_id) AS column_name
,ic.index_column_id
,ic.key_ordinal
,ic.is_included_column
,i.is_primary_key
,is_unique
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.object_id = OBJECT_ID('dbo.table_name');
Result:
How to replace the first instance of the string in T-SQL?
You can use the combination of stuff
, charindex
and len
function to replace the first occurrence of the string.
declare @database_name nvarchar(max);
declare @command_text nvarchar(max);
declare @update_command_text nvarchar(max);
set @database_name = 'MY_DATABASE';
set @command_text = '
RESTORE DATABASE [MY_DATABASE] FROM DISK = N''\\XYZABCMNO\MSSQL_Bak\MY_DATABASE.BAK''
WITH FILE = 1, MOVE N''MY_DATABASE'' TO N''M:\MSSQL_Data\MY_DATABASE.mdf''
, MOVE N''MY_DATABASE_Log'' TO N''N:\MSSQL_Log\MY_DATABASE.ldf''
, NOUNLOAD, REPLACE, STATS = 10
GO
'
set @update_command_text = (select stuff(@command_text, charindex(@database_name, @command_text), len(@database_name), 'TEST_'+@database_name))
select @command_text, @update_command_text
How to get all the computed columns in a database in SQL Server?
-- get the list of computed columns in a database
USE your_database_name_here;
GO
SELECT schema_name(o.schema_id) AS schema_name,
object_name(c.object_id) AS table_name,
c.name AS column_name,
type_name(user_type_id) AS data_type,
definition,
is_persisted
FROM sys.computed_columns c
INNER JOIN sys.objects o ON o.object_id = c.object_id
ORDER BY schema_name,
table_name,
column_name
How to search for a table or column in all the databases in a server
To search for a table across databases in a server
EXEC sp_MSforeachdb
'SELECT "?" AS DB, *
FROM [?].sys.tables
WHERE name like ''%invoice%'''
Sample output:
To search for a column in tables across databases in a server
EXEC sp_MSforeachdb
'SELECT "?" AS DB, object_name(object_id,db_id("?")) as TableName, *
FROM [?].sys.all_columns
WHERE name like ''%invoice%'''
How to check database restore history and backup file used for restore in SQL Server?
SELECT
rs.destination_database_name,
rs.restore_date,
bs.backup_start_date,
bs.backup_finish_date,
bs.database_name as source_database_name,
bmf.physical_device_name as backup_file_used_for_restore
FROM msdb.dbo.restorehistory rs
INNER JOIN msdb.dbo.backupset bs ON rs.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
ORDER BY rs.restore_date DESC;
How to split a string into a list of values in SQL?
I have created a function to split a string based on the delimiter value and return the result as a single column of values in a table.
CREATE OR ALTER FUNCTION dbo.fn_string_to_list (
@input_string NVARCHAR(MAX),
@delimiter NVARCHAR(10)
)
RETURNS @return_table TABLE (word NVARCHAR(MAX) NULL)
AS
BEGIN
DECLARE @position INT = 0;
DECLARE @next_position INT = 1;
DECLARE @word_length INT;
WHILE @next_position > 0
BEGIN
SET @next_position = CHARINDEX(@delimiter, @input_string, @position + 1);
SET @word_length = (CASE WHEN @next_position > 0 THEN @next_position ELSE LEN(@input_string) + 1 END) - @position - 1;
INSERT INTO @return_table (word) VALUES (LTRIM(RTRIM(SUBSTRING(@input_string, @position + 1, @word_length))));
SET @position = @next_position;
END
RETURN;
END
GO
Sample output:
How to search for a list of words contains in a string column?
To search whether the list of words contains in a column, you can implement it using a series of conditions with LIKE
and OR
operators. But to search for additional word, you need to add another condition.
SELECT * FROM dbo.my_table
WHERE description LIKE '%word1%'
OR description LIKE '%word2%'
OR description LIKE '%word3%'
Instead of the above approach, you can create the below function dbo.fn_contains
that search for the words in a string column. This function uses another function dbo.fn_string_to_list
which converts a string to a list of values.
CREATE OR ALTER FUNCTION dbo.fn_contains(
@string_to_search NVARCHAR(4000)
, @string_to_find NVARCHAR(4000)
)
RETURNS INT
AS
BEGIN
DECLARE @word_count INT;
DECLARE @word NVARCHAR(4000);
DECLARE @i INT = 1;
DECLARE @split_table TABLE (
id INT IDENTITY(1,1),
word NVARCHAR(4000)
)
INSERT INTO @split_table
SELECT word FROM dbo.fn_string_to_list (@string_to_find,' ');
SET @word_count = (SELECT COUNT(1) FROM @split_table)
WHILE @word_count >= @i
BEGIN
SET @word = (SELECT word FROM @split_table WHERE id = @i)
IF @string_to_search LIKE '%'+@word+'%' RETURN 1;
SET @i += 1;
END
RETURN NULL
END
GO
Sample query to search for a list of words in a string column.
SELECT description
FROM dbo.myTable
WHERE dbo.fn_contains(description,'word1 word2 word3') = 1
GO