How to separate out numbers from a string in SQL Server

At a time we will have a requirement to seperate out numbers from a string.

Use below query to seperate out numbers from the string:

DECLARE @strValue VARCHAR(256) = 'asdf1234.a1s2d3f4@@@'
DECLARE @IntPos INT
SET @IntPos = PATINDEX('%[^0-9]%', @strValue)
WHILE @IntPos > 0
BEGIN
SET @strValue = STUFF(@strValue, @IntPos, 1, '' )
SET @IntPos = PATINDEX('%[^0-9]%', @strValue )
END
SELECT @strValue

Different types of joins in SQL

We have three types of joins in SQL:

a. INNER JOIN

b. OUTER JOIN

c. CROSS JOIN

To understand how the joins work let us first create two table Employee and Department:

CREATE TABLE [dbo].[Employee]

(

[EmpID] [int] NOT NULL,

[FirstName] [nvarchar](250) NOT NULL,

[LastName] [nvarchar](250) NULL,

[Salary] [money] NULL,

[DeptID] [int] NULL

)

INSERT INTO [dbo].[Employee]

(

[EmpID]

,[FirstName]

,[LastName]

,[Salary]

,[DeptID]

)

VALUES

(1, 'Anil', NULL, 10000, 1)

,(2, 'Srikanth', NULL, 20000, 2)

,(3, 'Ramesh', NULL, 30000, 4)

,(4, 'Sunil', NULL, 40000, 1)

,(5, 'Suresh', NULL, 50000, 3)

,(6, 'Shravan', NULL, 60000, 6)

CREATE TABLE [dbo].[Department]

(

[DeptID] [int] NOT NULL

,[DeptName] [nvarchar](50)

)

INSERT INTO [dbo].[Department]

(

[DeptID]

,[DeptName]

) VALUES

(1, 'CSE')

,(2, 'ECE')

,(3, 'EEE')

,(4, 'MEC')

,(5, 'CIV')

,(1, 'ARE')

a. INNER JOIN:

It will give only the matching records from both the tables on the basis of joining column.

In this, joining column from the first table will be matched with the joining column from the second table for each and row available in the second table and only the matching records will be returned.

Most of the people think like we don't get more than number of rows available in joining tables.
That is if we have 4 rows in left table and 3 rows in right table then we may think like we will get maximum of 4 rows but that is not true.

Go through below example to know how we will get more number rows than we have in both the tables.

SELECT

E.[EmpID]

,E.[FirstName]

,E.[LastName]

,E.[Salary]

,E.[DeptID] AS DeptIDFromEmp

,D.[DeptID] AS DeptIDFromDept

,D.[DeptName]

FROM

[dbo].[Employee] E

INNER JOIN [dbo].[Department] D

ON E.[DeptID] = D.[DeptID]

Before looking at the result set returned by this query, guess how many rows this query will return.

Number of rows returned by this query is not equal to the number of rows available in left table (i.e)6.

Why because here the joining column is DeptID and for DeptID = 1 we have two matching rows from the right table. So it will return two rows for the record with DeptID = 1 form the left table that's why we will see more number of rows than present in left table.

Result:

b. OUTER JOIN:

We have three types of joins in this.

1. LEFT OUTER JOIN
2. RIGHT OUTER JOIN
3. FULL OUTER JOIN

In all these commands OUTER clause is optional, we need not to mention that in our query just we can write LEFT JOIN, RIGHT JOIN, FULL JOIN.

I. LEFT JOIN:

It will give all records from the left table and only matching records from right table.

If any value is not matching with the right table then it will return NULL value in the column coming from the right table.

SELECT

E.[EmpID]

,E.[FirstName]

,E.[LastName]

,E.[Salary]

,E.[DeptID] AS DeptIDFromEmp

,D.[DeptID] AS DeptIDFromDept

,D.[DeptName]

FROM

[dbo].[Employee] E

LEFT JOIN [dbo].[Department] D

ON E.[DeptID] = D.[DeptID]

Result:

If we see the result set for DeptID coming from Employee (DeptIDFromEmp) table we have value 6 in the last row and for the DeptID column (DeptIDFromDept) coming from Department table we have NULL value because for that value we don’t have any matching record from right table where as from the left table we have some value that is the reason it is showing up in the column coming from left table but not in the column coming from right table.

II. RIGHT JOIN:

It will give all the records from right table and only the matching records from left table.

If any value is not matching with the left table then it will return NULL value in the column coming from the left table.

SELECT

E.[EmpID]

,E.[FirstName]

,E.[LastName]

,E.[Salary]

,E.[DeptID] AS DeptIDFromEmp

,D.[DeptID] AS DeptIDFromDept

,D.[DeptName]

FROM

[dbo].[Employee] E

RIGHT JOIN [dbo].[Department] D

ON E.[DeptID] = D.[DeptID]

Result:

III. FULL JOIN:

It's a combination of both LEFT and RIGHT join that means it will give all matching records from both the tables, non matching records from left table (in this case it will insert NULL values in the columns coming from the left table) and non matching records from right table (in this case it will insert NULL values in the columns coming from the right table).

SELECT

E.[EmpID]

,E.[FirstName]

,E.[LastName]

,E.[Salary]

,E.[DeptID] AS DeptIDFromEmp

,D.[DeptID] AS DeptIDFromDept

,D.[DeptName]

FROM

[dbo].[Employee] E

FULL JOIN [dbo].[Department] D

ON E.[DeptID] = D.[DeptID]

Result:

CROSS JOIN:

It will return all the records from the right table for each record in left table.

For example if we have 2 rows in left table and 3 rows in right table then each record will be mapped with all the records from the right table so we will get 2*3 = 6 records.

Here we need not to mention any ON condition as it matches each record from left table with all the records from right table.

SELECT

E.[EmpID]

,E.[FirstName]

,E.[LastName]

,E.[Salary]

,E.[DeptID] AS DeptIDFromEmp

,D.[DeptID] AS DeptIDFromDept

,D.[DeptName]

FROM

[dbo].[Employee] E

CROSS JOIN [dbo].[Department] D

Result:

Hope you understood the joins, if you still have any questions please comment.

Inserting data into SQL table

Go through Create table script in SQL post to know about how to create the table.

In order to to insert the data into table we will use INSERT command.
Now, we are going to insert data into Employee table that we have created in Create table script in SQL post.

There are alternative ways to insert data into table:
Alternate1:
  INSERT INTO dbo.Employee 
  (
[EmpID]
,[FirstName]
,[LastName]
,[Salary]
  )
  VALUES 
  (
1
,'Anil'
,'Kumar'
,10000
  )
This is the best way to insert data into our table.
Because with this script we can identify which data we are inserting into which column.
We can also change the order of columns in insert script and can insert data accordingly.
Alternative2:
  INSERT INTO dbo.Employee 
  VALUES
  (
1
,'Anil'
,'Kumar'
,10000
  )
In this way we should know what is the ordering of columns inside the table in advance. 
As per the ordering of the columns it will insert the data into table.
Alternative3:
INSERT INTO dbo.Employee 
SELECT 
[EmpID]
,[FirstName]
,[LastName]
,[Salary] 

FROM dbo.Employee 

Inserting multiple rows at a time:
If you want to insert multiple values at a time them you can do it as shown below:
INSERT INTO dbo.Employee 
  VALUES
  ('1', 'Anil', 'Kumar', 10000)
  ,('2', 'Suresh', 'T', 10000)
  ,('3', 'Kumar', 'Anil', 10000)

In this way, you are selecting all the data available in Employee table and then reinserting it into Employee table.
Here i have taken the example of selecting data from Employee table but you can select from any table and insert it into Employee table.

If any column in Nullable then you can choose to ignore that column in your script, it will automatically insert NULL values in those columns but we should definitely insert some data into NOT NULL columns.