Import SQL Server data into Microsoft Excel using VBA

SQL Server Data Import to Excel using ADO;Import SQL Server data into Microsoft Excel using VBA;Excel-SQL Server Import-Export using VBA: ADO and QueryTabl

SQL Server Data Import to Excel using ADO

1) The function inserts SQL Server data to the target Excel range using ADO.

Function ImportSQLtoExcel(sheet As String, row As Long, column As Long, commandText As String) As Integer
     ' requires a reference to the object library "Microsoft ActiveX Data Objects 2.x Library" under Options > Tools > References... in the Visual Basic Editor.

    Dim rangesheet As String
    rangesheet = Sheets(sheet).Cells(row + 1, column).Address
    
    Dim database As String
    Dim UID As String
    Dim PWD As String
    
    database = Sheets("Configuration").Cells(2, Range("Configuration[[#All],[CONFIGURATION_DATABASE]]").column).Value
    UID = Sheets("Configuration").Cells(2, Range("Configuration[[#All],[UID]]").column).Value
    PWD = Sheets("Configuration").Cells(2, Range("Configuration[[#All],[PWD]]").column).Value
    
       
    Dim connectionsheet As String
    
    connectionsheet = OdbcConnectionStringSQLServer("SQL Server", Server, database, UID, PWD)
    
    Dim cnt As ADODB.Connection
    Set cnt = New ADODB.Connection
    cnt.ConnectionString = connectionsheet
    cnt.Open
    
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnt
    cmd.commandText = commandText
    cmd.CommandType = adCmdText
       
    ' Object type and CreateObject function are used instead of ADODB.RECORDSET,
    ' After late binding without reference to
    ' Microsoft ActiveX Data Objects 2.x Library
    Dim rs As Object
    Set rs = CreateObject("ADODB.RECORDSET")
    rs.ActiveConnection = cnt
    rs.Open commandText, cnt
  
'Deletes ListObjet or QueryTable if already exist

    If Sheets(sheet).ListObjects.Count > 0 Then 'created in Excel 2007 or higher
       
        For Each tbl In Sheets(sheet).ListObjects
            tbl.Delete
        Next tbl
    ElseIf Sheets(sheet).QueryTables.Count > 0 Then ' Created in Excel 2003
         For Each tbl In Sheets(sheet).QueryTables
            tbl.ResultRange.Clear
            tbl.Delete
        Next tbl
    End If
  

   With Sheets(sheet).ListObjects.Add(SourceType:=3, Source:=rs, Destination:=Range(Sheets(sheet).Cells(row, column).Address)).QueryTable
       
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCell
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .ListObject.Name = displayname
        .ListObject.ShowTotals = False
        .Refresh BackgroundQuery:=False
    End With
    
   Sheets(sheet).ListObjects(displayname).TableStyle = "TableStyleMedium9"
   ImportSQLtoExcel = 0
   
CloseRecordset:
    rs.Close
    Set rs = Nothing
CloseConnection:
    cnt.Close
    Set cnt = Nothing
       
End Function

Function OdbcConnectionStringSQLServer(ByVal Driver As String, ByVal Server As String, ByVal database As String, _
    ByVal Username As String, ByVal Password As String) As String

'SQL Server
  
     OdbcConnectionStringSQLServer = "Driver={" & Driver & "};Server=" & Server _
            & ";UID=" & Username & ";PWD=" & Password & ";Database=" & database
  

End Function

Win32Exception (0x80004005): The wait operation timed out

Win32Exception (0x80004005),Win32Exception (0x80004005) The wait operation timed out, The wait operation timed out,SQL Timeout expired
I was running an .NET console application that upon initial load pulls a list of items from a SQL server via a stored procedure. Within few seconds of loading the application, received the below error message:

Exception::System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out::Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.::.Net SqlClient Data Provider

Cause:
The problem was that the stored procedure took ~37 seconds to complete, which is slightly greater than the default timeout for a query to execute - 30 seconds. I figured this by executing the stored procedure manually in SQL Server Management Studio.

Resolution:
We need to set the CommandTimeout (in seconds) so that it is long enough for the command to complete its execution.

added the below line before filling the data adapter.

SqlCommand.CommandTimeout = 60; //60 seconds that is long enough for the stored procedure to complete.

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

SQL Errors and Solutions,The conversion of a varchar data type to a datetime data type resulted in an out-of-range value,SQL,SQL tips

SQL Server Error Message:

 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

This error occurs when the varchar value does not form a valid date.

Error Message:

Server: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Causes:

This error occurs when trying to convert a string date value into a DATETIME data type but the date value contains an invalid date. The individual parts of the date value (day, month and year) are all numeric but together they don’t form a valid date.


To illustrate, the following SELECT statements (all based on US date format MM/DD/YYYY)will generate the error:

SELECT CAST('02/29/2006' AS DATETIME) -- 2006 Not a Leap Year   

SELECT CAST('06/31/2006' AS DATETIME) -- June only has 30 Days   

SELECT CAST('13/31/2006' AS DATETIME) -- There are only 12 Months

SELECT CAST('01/01/1600' AS DATETIME) -- Year is Before 1753


Another way the error may be encountered is when the format of the date string does not conform to the format expected by SQL Server as set in the SET DATEFORMAT command. For example, in United

To illustrate, if the date format expected by SQL Server is in the MM-DD-YYYY (US date format) format, the following statement will generate the error:

SELECT CAST('31-01-2006' AS DATETIME)

Solution/Workaround:

To avoid this error from happening, you can check first to determine if a certain date in a string format is valid using the ISDATE function. The ISDATE function determines if a certain expression is a valid date. So if you have a table where one of the columns contains date values but the column is defined as VARCHAR data type, you can do the following query to identify the invalid dates:

SELECT * FROM [dbo].[Orders]
WHERE ISDATE([OrderDate]) = 1

Once the invalid dates have been identified, you can have them fixed manually then you can use the CAST function to convert the date values into DATETIME data type:

SELECT CAST([OrderDate] AS DATETIME) AS [Order Date]
FROM [dbo].[Orders]

Another way to do this without having to update the table and simply return a NULL value for the invalid dates is to use a CASE condition:

SELECT CASE ISDATE([OrderDate]) WHEN 0
THEN CAST([OrderDate] AS DATETIME)
ELSE CAST(NULL AS DATETIME) END AS [Order Date]
FROM [dbo].[Orders]

Unable to find the requested .Net Framework Data Provider. It may not be installed.

SQL Errors and Solutions,.NET Errors and Solutions,Unable to find the requested .Net Framework Data Provider. It may not be installed.,.NET

I got this error "Unable to find the requested .Net Framework Data Provider. It may not be installed." when I try to retrieve the data from SQL Server.

Reason:


Whoo!!

I had wrongly mentioned the Data provider name in my dll.

I had mentioned as

Conndll.ProviderName = "System.Data.Sqlclient" 'c in client should have been upper case letter

instead of

Conndll.ProviderName = "System.Data.SqlClient"

Convert CYYMMDD format to datetime - SQL

Convert CYYMMDD to DateTime,Convert CYYMMDD to DateTime Format,SQL,SQL Query,AS400 datetime
In the date format CYYMMDD(AS400 date format), C is Century.

If C is 0 the year is 19XX, and it is 20XX if C is 1(Where XX can be any
two digit year).

YYMMDD is Year Month and Day(All two digit).

In CYYMMDD format, today's date (May 27, 2010) would be 1100527.

It is '0' for 19 and '1' for 20 to make our comparisons easy.

But the '0' will not present in the table data as the leading zeroes of numeric will be truncated.

The following query converts the CYYMMDD format to datetime format:

SELECT dbo.TBL.ACTDT  AS ActualDate, CAST(CASE WHEN SUBSTRING(RIGHT('00' + CAST(dbo.TBL.ACTDT AS VARCHAR), 7), 1, 1) = '0' THEN '19' ELSE '20' END + SUBSTRING(RIGHT('00' + CAST(dbo.TBL.ACTDT AS VARCHAR), 7), 2, 2) + SUBSTRING(RIGHT('00' + CAST(dbo.TBL.ACTDT AS VARCHAR), 7), 4, 4) AS DATETIME) AS [Converted Date] FROM TBL


Query Result:

Convert CYYMMDD Format to datetime format

Invalid object name 'INFORMATION_SCHEMA.tables' - SQL Error

Invalid object name 'INFORMATION_SCHEMA.tables',SQL Errors and Solutions,SQL

The error Invalid object name 'INFORMATION_SCHEMA.tables' was thrown when I executed the  following query,

Select * from INFORMATION_SCHEMA.tables

for a particular database.

Reason: The particular database was case sensitive.

Solution:
I re-ran the same query with upper-case letters and it worked fine.

Select * from INFORMATION_SCHEMA.TABLES - did the trick for me.

Convert YYYYMMDD to DateTime SQL

Convert YYYYMMDD to DateTime,Convert YYYYMMDD to DateTime Format,SQL,SQL Query
The following SQL query converts the date stored as decimal or numeric in the form of YYYYMMDD to datetime.


1) SELECT CAST(CAST(YourDateField AS VARCHAR(8)) AS DATETIME)


For example:

SELECT CAST(CAST(20060204 AS VARCHAR(8)) AS DATETIME) AS [DateTime]


will be displayed as

2006-02-04 00:00:00.000

2) CONVERT(datetime, YourDateField) AS [DateTime]

     For example:


   CONVERT(datetime, '20060204') AS [DateTime]

   will be displayed as

     02/04/2006

Convert Julian Date YYYYDDD to Datetime Format

Convert YYYYDDD to datetime,Convert Julian Date YYYYDDD to Datetime Format,Convert Julian Date YYYYDDD to Gregarian Format,SQL,SQL Query
I got a requirement to display the date which is the format 7-digit YYYYDDD (Julian Date) to display in Gregarian format.

This is the query I wrote to convert YYYYDDD format to YYYY-MM-DD format.

YYYY - 4 - digit Year
DDD -  number of the day in the year

SQL Query:

SELECT  [Julian Date], DATEADD(DAY, [Julian Date]% 1000 - 1, DATEADD(YEAR,


[Julian Date]/ 1000 - 1900, 0)) AS [Gregarian Date]


FROM dbo.Test
 
Sample Output:
 

Import Microsoft Access 2007 Database Tables into SQL Server 2008

Import Microsoft access 2007 database to SQL Server,Import Microsoft access 2007 database to SQL Server 2008,Import Microsoft access database to SQL Server,Import access 2007 database to SQL Server,Import access 2007 database to SQL Server 2008,Import Ms access 2007 database to SQL Server,Import Ms access database to SQL Server,Import Ms access 2007 database to SQL Server 2008,
Import MS access database, Import MS access 2007 database to SQL, SQL, Microsoft Access 2007,Import access 2007,Import MS access 2007,Import Microsoft access 2007 database tables to SQL Server,Import Microsoft access 2007 database tables to SQL Server 2008,Import Microsoft access database tables to SQL Server,Import access 2007 database tables to SQL Server,Import access 2007 database tables to SQL Server 2008,Import Ms access 2007 database tables to SQL Server,Import Ms access database tables to SQL Server,Import Ms access 2007 database tables to SQL Server 2008,
Import MS access database tables, Import MS access 2007 database tables to SQL, SQL, Microsoft Access 2007,Import access 2007 tables,Import MS access 2007 tables,

This article explains how to import Access 2007 database tables into MS SQL Server 2008.

To import data from a Microsoft Access 2007 database, we must install the OLEDB Provider for Microsoft Office 12.0 Access Database Engine.

1) Goto SQL Server Management Studio, Object Explorer

Right Click the desired database and select Tasks -> Import Data.

Follow the below steps to import data into a SQL Server 2008 database.




2)Select the Data source from which you want to import the data.
Because of the difference between the database engine of Microsoft Access 2007 and earlier version of Microsoft Access, it is not possible to connect to the Access 2007 database using data source “Microsoft Access”. You can use this if you wish to import data from a MDB format, but not an ACCDB from Access 2007.


If you have properly installed the 2007 Office System driver, you will see another Data Source option: “Microsoft Office 12.0 Access Database Engine.”

Step2: Select Microsoft Office 12.0 Access Database Engine

3) Click the Properties button to open the Data Link Properties window:

Step 3: Enter access 2007 database Path

Enter the full path of the Access 2007 database in Data Source Name. Click Test Connection to make sure the connection succeeds.


4) Click Ok to close the properties page. Click Next to choose the destination database

step4: Choose destination database

5. Click Next to continue.

Step 5: Select option to copy

choose option for Copy data from one or more tables or views.


6) Click Next, and select the Source Tables or Views to import. If necessary use the Edit Mapping button to map the Columns correctly.

Step 6: Select tables/view to copy

7) Click Next to continue.

 The next screen of the wizard shows two options.
 First one is to Execute Package Immediately and second one is to save SSIS package which can be used in the Business Intelligence Development Studio Project.

8) Click Next and Finish.

It shows the progress of copying data and lists out errors if any.

On successful import, you can expand the tables on the database and see the imported tables from Access database.









 

Display row count for all SQL tables

The following T-SQL script returns the number of rows in each table for a database.


SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS [Full Table Name], SCHEMA_NAME(t.schema_id) AS [Schema Name], t.name AS [Table Name],

i.rows AS [Row Count] FROM sys.tables AS t INNER JOIN

sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2
WHERE (t.type = 'U')
   

 

Sample Output:

 

Displaying row count for all tables

 

Concatenation of NULL strings in SQL Server

Concatenation of String with a null value always yields a NULL result. When we concatenate two field values using ‘+’ operator in SQL Server, it yields a NULL result when either one of the value is NULL.

For example , SELECT FirstName + LastName AS Name yields NULL value where FirstName is ‘XX’ and LastName is Null.



But the actual requirement should be ‘XX’ instead of NULL.



This can be achieved in two ways:



1. Using SET CONCAT_NULL_YIELDS_NULL option

If The SET CONCAT_NULL_YIELDS_NULL option is OFF, concatenating a null value with a string yields the string itself , as the NULL value will be treated as an empty string



Example:

SET CONCAT_NULL_YIELDS_NULL OFF


GO


SELECT FirstName + LastName AS Name


GO


SET CONCAT_NULL_YIELDS_NULL ON


GO


SELECT FirstName + LastName AS Name


GO

Result:

XX


NULL



By default, CONCAT_NULL_YIELDS_NULL option is ON.

2) Using ISNULL Keyword



The ISNULL option replaces the NULL value with specified value. In this example if the Last Name is NULL value, then it will be replaced by empty string.

SELECT FirstName + ISNULL( LastName,’ ‘) AS Name

Result:

XX

SQL Query: Removing leading zeroes from SQL Server field

The SQL Query for Removing leading zeroes from SQL Server field

The following SQL Query removes the leading zeroes from "Field" field

Select replace(ltrim(replace(Field1, '0', ' ')) , ' ', '0') FROM TestTable

 

What is SQL Injection

 

SQL Injection is an attack of non-valid inputs passed through web application for execution by a backend database , simply It is a trick to inject data to SQL query/command as an input possibly via web pages.

Best example for this, when a user login the web page that user name and password and make SQL query to the database to check if a user has valid name and password. With SQL Injection, it is possible for us to send crafted user name and/or password field that will change the SQL query and grant us.

SQL Query :

sqlquery = “ SELECT USERNAME FROM USERLOGINTABLE WHERE USERNAME = ‘ “ + strusername + “ ’ AND PASSWORD = ‘“ + strpwd + “ ’ ”;

sqlqueryresult = GetQueryresult(sqlquery);

if (sqlqueryresult = string.empty)

{

Response.write(“User login failed”);

}

Else

{

Response.redirect(“home.aspx”);

}

User passes ‘VIJI’ and ‘PASS’  as username and password respectively. If the user is a valid by executing the above SQL command, web page redirect to home page.

Look here, if user passes the below inputs Strusername as ‘ OR ‘ ‘ = ‘ and Strpwd as ‘ OR ‘ ‘ = ‘ then dynamic query will be

“ SELECT USERNAME FROM USERLOGINTABLE WHERE USERNAME = ‘ OR ‘ ‘ = ‘ AND PASSWORD = ‘ OR ‘ ‘ = ‘ “

Few judgment of this query:

• There is no syntax error
• There is no conflict between the operators.
• Inputs are not valid.

Web application will redirect the home page even input are invalid because result of the query will be true. The query compares the first single quotation and another quotation (means nothing) then OR is an operator. When comparing nothing to =, it returns true. Same execution is applied for password. These kind inputs are called vulnerable inputs to SQL commands.

Disadvantages

SQL injection provides a facility to the net hackers to pull the data from the backend database by supplying the vulnerable inputs.

Will Continue writing on the

Attacks of the SQL Injection

• Select Command.
• Insert Command.
• Using SQL Stored Procedures.

and how to prevent SQL injection in the upcoming articles.

SQL Editor Shortcut Keys

SQL Editor is a code editor and explorer for SQL Server databases.

The following is a list of Shortcut keys used in the SQL Editor.

Shortcut Key	Function
F1	Windows Help File
F2	Toggle Full screen Editor
F3	Find Next Occurrence
<SHIFT> <F3>	Find Previous Occurrence
F4	Describe Table, View, Procedure, Function, or Package in popup window
F5	Execute as Script
F6	Toggle between SQL Editor and Results panel
F7	Clear All Text
F8	Recall previous SQL statement
F9	Execute statement
<CTRL> F9	Verify statement without execution (parse)
<SHIFT> F9	Execute current statement at cursor
F10	Popup Menu
<CTRL> A	Select All Text
<CTRL> C	Copy
<CTRL> E	Execute Explain Plan on the Current Statement
<CTRL> F	Find Text
<CTRL> G	Goto Line
<CTRL> L	Converts Text to Lowercase
<CTRL> M	Make Code Statement
<CTRL> N	Recall Named SQL Statement
<CTRL> O	Opens a Text File
<CTRL> P	Strip Code Statement
<CTRL> R	Find and Replace
<CTRL> S	Save File
<SHIFT> <CTRL> S	Save File As
<CTRL> T	Columns Dropdown
<CTRL> U	Converts Text to Uppercase
<CTRL> V	Paste
<CTRL> X	Cut
<CTRL> Z	Undo Last Change
<CTRL>.	Display popup list of matching tablenames
<SHIFT> <CTRL> Z	Redo Last Undo
<ALT> <UP>	Display Previous Statement
<ALT> <DOWN>	Display Next Statement (after <ALT> <UP>)
<CTRL><HOME>	In the data grids, goes to the top of the recordset
<CTRL><END>	In the data grids, goes to the end of the recordset
<CTRL><SPACE>	Completely expand dependency tree views
<CTRL><TAB>	Cycles through the collection of MDI Child windows

Difference between UNION and UNION ALL in SQL

In this article we will see the difference between UNION and UNION ALL Sql Keywords.

Syntax:

[SQL Statement 1]


UNION [ALL]


[SQL Statement 2]

Where ALL statement is optional.

Both the Sql statements must consist of equal number of fields.


Similarties:


The UNION and UNION ALL Sql Keywords are used to combine two or more sql queries and return the result set consisting of a single set of all queries mentioned in there.


Differences:

The main difference between UNION and UNION ALL is that UNION retrieves distinct values (With out duplicates) where as UNION ALL retrieves all values from the result set including duplicates.


Example

Consider Employees Table of NorthWind database.


UNION ALL

The following query is used to display the EmployeeId and lastName.

SELECT EmployeeID, lastName
FROM [dbo].[Employees]

UNION ALL

SELECT EmployeeID, lastName
FROM [dbo].Employees


RESULT


EmployeeId LastName

5 Buchanan
8 Callahan
1 Davolio
9 Dodsworth
5 Buchanan
8 Callahan
1 Davolio
9 Dodsworth

The repetition of same records twice. Thus duplicate records exists in this result set.

UNION

The following query is used to display the EmployeeId and lastName.

SELECT EmployeeID, lastName
FROM [dbo].[Employees]

UNION

SELECT EmployeeID, lastName
FROM [dbo].Employees


RESULT

EmployeeId LastName

5 Buchanan
8 Callahan
1 Davolio
9 Dodsworth


Here only distinct values are returned. Thus no duplicate records exists in this result set.


Same artcicle available underhttp://www.dotnetspider.com/resources/29704-Difference-between-UNION-UNION-ALL-SQL.aspx

Script to drop all stored procedures from the database

The following SQl stored procedure dropAllSpproc is used to drop all stored procedures from the database.

We need to mention the schema name in the stored procedure.

/*The procedure declares the cursor to iterate through all SP List*/

/*This procedure retrieves SP Name from sysobjects based on type 'p'

/*The type 'p' indicates - it is a stored procedure */





CREATE PROCEDURE [dbo].[dropAllSpProc] AS



--Declaration of Cursor type

DECLARE spDropCursor1 cursor for




--Retrieve the names of Stored Procedures




SELECT name

FROM sysobjects

WHERE type = 'P' -- 'P' indicates Stored Procedure



--Open the spDropCursor

OPEN spDropCursor1



--Declaraion of Variable to store Stored procedure name



DECLARE @spName varchar(100)

DECLARE @sqlStr varchar(100)



--Fetch the first name into SPName variable


FETCH NEXT FROM spDropCursor1 INTO @spname



--Start the Loop

WHILE @@fetch_status = 0


begin


SET @sqlStr = 'drop procedure ' + @spname


--Execute the drop Statement


EXEC @sqlStr



--Start Fetching other stored procedure Name

fetch next from spDropCursor1 into @spname



end



--Clean Up

close spDropCursor1



deallocate spDropCursor1



Go

Combine Columns in SQL Server

In this article, we can see the various sql queries to combine two columns.



Concatenation Using + Operator

Syntax:

SELECT (ColumnA + ColumnB) AS ColumnZ
FROM Table;

Example


SELECT [First Name], [Last Name], [First Name] + ' ' + [Last Name] AS 'Full Name' FROM Employee

In this example we have combined First and Last Name of a person to get the Full Name.


Result


First Name Last Name Full Name

Jack Benny Jack Benny
Michael Hussain Michael Hussain
Roberts Tennison Roberts Tennison



Concatenation Using Union Operator

The sql keyword Union is used to combine the two columns.

Syntax:

SELECT ColumnA AS ColumnZ
FROM Table

UNION --Union Keyword to combine two columns

SELECT ColumnB AS ColumnZ
FROM Table
ORDER BY ColumnZ


Example

SELECT user_login AS user_idee, employee FROM employee, grpmebrs
WHERE USER_ID = user_login
UNION
SELECT secgroup AS user_idee, employee FROM employee, grpmebrs
WHERE USER_ID = user_login


This will also list the same thing.

Effectivly the result is the same.

Combine Null Columns

The Concatenation using "+" Fails when either column is Null.

Solution:

The isNull Function is used to check whether the column is Null.


Example With isNull Function


Select isnull(CAST(Customer.Contactid as nvarchar(10)),'') +'-'+ isnull(Customer.Firstname,'') +'-'+ isnull(Customer.LastName,'') as [Full Name] from Customer where customerid = 1823


Result

1823-Michael-Dizousa

SQL Commands to Set Permissions

The following SQL keywords are used to change or set someone’s permissions:

1. GRANT

2. DENY

3. REVOKE.


GRANT:

GRANT keyword is used to set the permission to particular user. The permissions may include database, create table, create view etc.

Example

Use TESTDB
GRANT CREATE TABLE TO ALL; --Allows all users to create table in TESTDB Data base.
GO


DENY:

Denies a permission to a user. The permissions may include database, create table, create view etc.

Example

Use TESTDB
DENY CREATE TABLE TO USER1; --Denies permission on create table in TESTDB Data base to USER1.
GO

REVOKE:

The REVOKE keyword is used to remove a previously granted or denied permission.

Example

Use TESTDB
REVOKE GRANT OPTION FOR CREATE TABLE TO USER1; --Revokes the permission on create table in TESTDB Data base to USER1.
GO

Difference between STUFF function and Replace Function in SQL ?

The STUFF function is used to overwrite the characters of string.

The Replace function is used to replace the occurance of a particular string with the specified string in all occurances.


STUFF Syntax:

STUFF (String, StartPos, LengthofReplaceChar, ReplaceString)

String - String to be overwritten
StartPos - Starting Position for overwriting
LengthofReplaceChar - Length of replacement string
ReplaceString - String to overwrite

Example


Declare @OriginalString Varchar(100)
Declare @ResultString Varchar (100)
Declare @ReplaceString Varchar(100)

SET @OriginalString = 'ALAMO'
SET @ReplaceString = 'Welcome '

SET @ResultString = SELECT STUFF(@OriginalString, 1,7, @ReplaceString)

Print 'Result: ' + @ResultString




Output

It will erase the letter A(First Letter) and inject 'Welcome ' in that position

Result: Welcome Lamo


REPLACE Syntax:

REPLACE (String, StringToReplace,StringTobeReplaced)

String - Input String
StringToReplace - The portion of string to replace
StringTobeReplaced - String to overwrite


Example


Declare @OriginalString Varchar(100)
Declare @ResultString Varchar (100)
Declare @ReplaceString1 Varchar(100)
Declare @ReplaceString2 Varchar(100)

SET @OriginalString = 'ALAMO'
SET @ReplaceString1 = 'A'
SET @ReplaceString2 = 'V'


SET @ResultString = SELECT REPLACE(@OriginalString, @ReplaceString1, @ReplaceString2)

Print 'Result: ' + @ResultString



Output

It will replace the letter A in the whole string

Result: VLVMO

Retrieve number of records from the table

There are many methods available to retrieve the number of records from the table.

Some of them are:



1.

SELECT COUNT(*)
FROM MyTable



2.

SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID(MyTable)
AND indid < 2




Both the above methods will return the number of records in the Sql Server table.