How to remove everything after the second decimal(or any special character) and count the occurrence of any character in the string in MS SQL Server DB.

 1.       Remove everything after the second decimal(or any special character)

Suppose we have a string in which we have multiple decimals and we have a requirement to cut the string at the occurrence of the second decimal then.

DECLARE @startParam varchar(50)

Set @startParam ='1111.000.00.00.00'

select left(@startParam, charindex('.', @startParam, charindex('.', @startParam)+1)-1);

O/P:-  1111.000

Here, we have declared one variable as “@StartParam” with the length of 50 characters and passed the value with multiple decimals in the string, so according to the condition, we removed everything after the second decimal in the select statement.

2.       Count the occurrence of any character

When we have same string as given above and as per condition needs to count the decimals(dot) in the string then.

Select (len(@startParam) - len(replace(@startParam,'.','')))

O/P:- 4

What other ideas can you add to this post that I may have not mentioned?

How to find Minimum and Maximum of Consecutive Dates in SQL server

When we have a condition to find the min and max of date from two different consecutive date columns from the same table with different id as shown below.

Query:- Select * from Project

O/P:-     

Id	StartDate	EndDate
8	2015-10-01	2015-10-02
9	2015-10-02	2015-10-03
10	2015-10-03	2015-10-04
11	2015-10-13	2015-10-14
12	2015-10-14	2015-10-15
13	2015-10-28	2015-10-29
14	2015-10-30	2015-10-31

Now we have the condition is to find Min and Max from these consecutive dates

Query:- WITH set1 AS (

                                                SELECT dd dt,ROW_NUMBER() OVER(ORDER BY dd) Rnum

                                                FROM

                                                (select startdate as dd from Project

                                                union select enddate  as dd from Project

                                                ) fnl

                                                GROUP BY dd

                                        )

SELECT MIN(dt) mindt,MAX(dt) maxdt

FROM set1

GROUP BY DATEDIFF(day,Rnum,dt)

 O/P:-

Mindt	Maxdt
2015-10-01	2015-10-04
2015-10-13	2015-10-15
2015-10-28	2015-10-31

 

What other ideas can you add to this post that I may have not mentioned?

How to remove extra space between the string in SQL Server Database

We get such type of condition when we concatenate multiple columns data into one output column and those column data have extra space.

For E.g: We have declared one variable and entered some dummy data as shown below.

Query:-

DECLARE @TestString1 AS VARCHAR(100)

SET @TestString1 = 'Address 1,      Address2,    Address3,     123'

print @TestString1;

Output:-   Address 1,      Address2,    Address3,     123

Here we are getting extra space between the addresses and condition to remove the extra space between the string then

Query:-

DECLARE @TestString2 AS VARCHAR(100)

SET @TestString2 = 'Address 1,      Address2,    Address3,     123'

SET @TestString2= REPLACE(REPLACE(REPLACE(@TestString2,' ',' %'),'% ',''),'%','')

print @TestString2;

Output:- Address 1, Address2, Address3, 123

In this query, we have replaced the extra space through a single space with the help of a wildcard operator.

What other ideas can you add to this post that I may have not mentioned?

Date and Time Formats in MS SQL Server Database

Convert() is basically used to convert any value in specified data type, Getdate() is used to fetch the date and time from the server where the database is stored. Queries to fetch date and time from database in a different format is shown below

1.  SELECT GETDATE() AS [Result] -- 2020-01-07 02:44:17.323

2.  SELECT CONVERT(VARCHAR(30),GETDATE(),100) AS [Result] -- Jan  7 2020  2:44AM

3.  SELECT CONVERT(VARCHAR(30),GETDATE(),101) AS [Result] -- 01/07/2020

4.  SELECT CONVERT(VARCHAR(30),GETDATE(),102) AS [Result] -- 2020.01.07

5.  SELECT CONVERT(VARCHAR(30),GETDATE(),103) AS [Result] -- 07/01/2020

6.  SELECT CONVERT(VARCHAR(30),GETDATE(),104) AS [Result] -- 07.01.2020

7.  SELECT CONVERT(VARCHAR(30),GETDATE(),105) AS [Result] -- 07-01-2020

8.  SELECT CONVERT(VARCHAR(30),GETDATE(),106) AS [Result] -- 07 Jan 2020

9.  SELECT CONVERT(VARCHAR(30),GETDATE(),107) AS [Result] -- Jan 07, 2020

10. SELECT CONVERT(VARCHAR(30),GETDATE(),108) AS [Result] -- 02:45:38

11. SELECT CONVERT(VARCHAR(30),GETDATE(),109) AS [Result] -- Jan  7 2020  2:45:44:427AM

12. SELECT CONVERT(VARCHAR(30),GETDATE(),110) AS [Result] -- 01-07-2020

13. SELECT CONVERT(VARCHAR(30),GETDATE(),111) AS [Result] -- 2020/01/07

14. SELECT CONVERT(VARCHAR(30),GETDATE(),112) AS [Result] -- 20200107

15. SELECT CONVERT(VARCHAR(30),GETDATE(),113) AS [Result] -- 07 Jan 2020 02:46:14:633

16. SELECT CONVERT(VARCHAR(30),GETDATE(),114) AS [Result] -- 02:46:19:930

17. SELECT CONVERT(VARCHAR(30),GETDATE(),120) AS [Result] -- 2020-01-07 02:46:25

18. SELECT CONVERT(VARCHAR(30),GETDATE(),121) AS [Result] -- 2020-01-07 02:46:30.080

19. SELECT CONVERT(VARCHAR(30),GETDATE(),126) AS [Result] -- 2020-01-07T02:46:43.200

20. SELECT CONVERT(VARCHAR(30),GETDATE(),127) AS [Result] -- 2020-01-07T02:46:49.050

21. SELECT CONVERT(VARCHAR(30),GETDATE(),130) AS [Result] -- 12 ????? ?????? 1441  2:47:14:

22. SELECT CONVERT(VARCHAR(30),GETDATE(),131) AS [Result] -- 12/05/1441  2:47:25:077AM

23. SELECT DATENAME(MONTH, GETDATE())

    + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) 

                                          AS [Result] -- March 09,  2022 

What other ideas can you add to this post that I may have not mentioned?

What are Deadlock queries and how to identify the locked SQL statements.

Deadlock condition occurs when two or more user is trying to perform the operation on the same data or in other words transaction override explicitly so the record is locked by each other.

Suppose we have two sessions and we are trying to update the same record. 

For E.g:- 

Session 1:-  Suppose we have session-id “101” for session 1.

Query:- Create Table Emp (Id Number(10),Name Varchar(100));

O/P:- Table Created

Now we are inserting the records into the table

Query:- Insert Into Emp Values(1,'Jon');

Insert Into Emp Values(2,'Greg');

Commit;

O/P:- 1 row inserted.

          1 row inserted.

          Commit complete.

Query:- Select * from Emp;

O/P:-

Id	Name
1	Jon
2	Greg

Condition:-  Now we will update the name of the employee for id 2 but we will not commit this transaction.

Query:- Update Emp Set Name=’Ted’ where id=2;

After this we are starting a new session.

Session 2:- Here we have session-id is “201” for session 2.

In this new session “201” we are will try to update the same record where id is 2.

Query:- Update Emp Set Name=’Paul’ where id=2;

This time DeadLock condition occurs and database enter into the deadlock trap, We need to execute the below query from third session to check what’s going on? why query is taking so much time to update the single record in session 2 (i.e session-id “201”).

Query:- SELECT 'Session-Id :'||' '||v1.sid ||' '||'Is Blocking'||' Session-id: '

            || v2.sid as SessionLockInfo,

Qr.sql_fulltext as QueryLocked

from   v$lock v1, v$lock v2 ,

(select a.sid, a.username,b.sql_id, b.sql_fulltext from v$session a, v$sql b

where a.sql_id = b.sql_id and a.status = 'ACTIVE' and a.username != 'SYS') Qr

where  v1.block =1 and v2.request > 0

and v1.id1=v2.id1 and v1.id2=v2.id2 and v2.sid =Qr.sid;

O/P:-

SessionLockInfo	QueryLocked
Session-Id : 101 Is Blocking Session-id: 201	Update Emp set Name='Paul' Where id=2

Deadlock condition automatically clear once the transaction for session 1(i.e session-id “101”) is completed or canceled, transaction completed means the user needs to hit the COMMIT or ROLLBACK statement in the session 1(i.e session-id “101”), then the session automatically releases.

What other ideas can you add to this post that I may have not mentioned?

Compare two arguments in oracle through "NULLIF" function

Oracle NULLIF() function accepts two arguments. It returns a null value if both arguments are equal. In case the arguments are not equal, the NULLIF() function returns the first argument.

For E.g:-

Condition 1:- If Expression 1 and Expression 2 are the same then it will return the NULL value.

Query:- SELECT NULLIF('Database Dynasty', 'Database Dynasty') as CheckArgument 

               FROM dual;

O/P:- Null

Condition 2:- If Expression 1 and Expression 2 are different then it will return the first argument.

Query :- SELECT NULLIF('Database', 'Database Dynasty') as CheckArgument 

               FROM dual;

O/P:- Database

What other ideas can you add to this post that I may have not mentioned?

Query to check the list of the dependent tables used in view

Suppose we have a requirement to generate the report of fetch the data from the database that shows the information about the view like schema where the view is created, referenced owner, table etc.

Query:-

select owner as View_Schema,

       name as View_Name,

       referenced_owner as Referenced_Schema,

       referenced_name as Referenced_Name,

       Referenced_Type

from sys.dba_dependencies

where type='VIEW'

      and referenced_type in ('TABLE','VIEW')

      and owner = 'Enter the name of the schema'

      and name = 'Enter the name of the view' 

order by owner, view_name;  

O/P:-

View_Schema	View_Name	Referenced_Schema	Referenced_Name	Referenced_Type
EMP	Vw_EmployeeDetails	HR	EmployeeDetails	Table

View_Schema          -> Where the view is created

View_Name               -> Name of the view

Referenced_Schema-> Referenced schema where the table is available i.e used in the view.

Referenced_Type      -> Type of reference like table

What other ideas can you add to this post that I may have not mentioned?