March 2009 Entries
To find a position of particular string/Char in the field of table, it is done using patindex. It might be well known for most of them. But what i found in that may be known to only few people. I want to share with everyone. Table: tblNames SELECT Name FROM tblNames Name Nagendra John Edward 3 row(s) affected. Example 1: To find Char or String, here is the way. ( Many of them know this ) SELECT PATINDEX('John', name) as Pos, Name FROM tblNames Pos Name 0 Nagendra 1 John 0 Edward 3 row(s) affected....
Lets see how to export SQL Server data into Excel. First we have to enable the Ad Hoc Distributed Queries option. Enable Ad Hoc Distributed Queries EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'Ad Hoc Distributed Queries', 1; GO RECONFIGURE; GO After enabling it, we can proceed further to write the query for exporting the SQL Serer Data. Export into Excel Query: INSERT INTO OPENROWSET('Microsoft.ACE.O... 'Excel 8.0;Database=C:\testing.xls;', 'SELECT Name,...
If you have two queries and you have to execute one after another with some amount of delay, then that is possible in SQL Server. Lets say, your first query fills up the table with some records. Lets assume that, you will get some responses based on this records which will fill other table. Now your second query should run based on the responses table. Then you can make a delay for quite amount of time. Lets see a simple example of using waitfor and delay in SQL Server: Syntax: WAITFOR DELAY 'HH:MM:SS'...
If you want to get the n number of rows dynamically, then we need use variable. It can be achieved by two different methods. Method 1: DECLARE @MyCount VARCHAR(5) DECLARE @sql NVARCHAR(200) -- note nvarchar datatype should be used, since it is for dynamic query SELECT @MyCount =2 SELECT @sql = 'SELECT TOP ' + @MyCount + ' * FROM TBLNAMES' EXEC sp_executesql @sql Method 2: DECLARE @MyCount int SET @MyCount = 2 SELECT TOP (@MyCount) * FROM TBLNAMES; First method would be the standard method, many people...
Lets see how to copy an existing table to new table in SQL Server. There are two options. They are Copy only the structure of an existing table into new table Copy only the structure with data of an existing table into new table Copy only the structure of an existing table into new table: SELECT * INTO tblNew FROM tblOld WHERE 1=2 The above query will copy the structure of an existing table(tblOld) into the new table(tblNew). Copy only the structure with data of an existing table into new table:...
As everyone knows, DATE can be formatted in different ways and the usual way will be using CONVERT Syntax: SELECT CONVERT(VARCHAR(30), GETDATE(), 106) Where as 106 can be replaced by 101, 110, etc according to the requirements. Custom Format: If we need custom date format(19-March-2009), which is not a pre-defined format in CONVERT, then consider the below: Syntax: SELECT DATENAME(DAY,GETDATE()) + '-' + DATENAME(MONTH,GETDATE()) + '-' + DATENAME(YEAR, GETDATE()) I know, this is just a small thing....
Formatting numbers to add leading zeros can be done in SQL Server. It is just simple. Lets create a new table and see how it works: CREATE TABLE Numbers(Num INT); Table Created. Lets insert few values and see: INSERT Numbers VALUES('12'); INSERT Numbers VALUES('112'); INSERT Numbers VALUES('12'); INSERT Numbers VALUES('122'); INSERT Numbers VALUES('122'); 1 row(s) affected. 1 row(s) affected. 1 row(s) affected. 1 row(s) affected. 1 row(s) affected. Now we can see how the numbers are formatted with...
I have to concatenate first name with last name. I got too many NULL values which it should not. Then I found the reason behind it. Data in TblNames : FIRST_NAME LAST_NAME Edward Jones Jason NULL John Smith Robert NULL 4 row(s) affected. Expected Result: NAME Edward Jones Jason John Smith Robert 4 row(s) affected. Before finding the solution: SELECT FIRST_NAME + ' ' + LAST_NAME AS [NAME] FROM TBLNAMES; NAME Edward Jones NULL John Smith NULL 5 row(s) affected. I realized the if any of the columns(FIRST_NAME...
I have came across a situation that i have to assign a value to a variable using dynamic query. I need the data which is stored in the table T1, but I have the table name T1 which is stored in other table M1. At the same time, i need to store the value in variable which will be used in various places in my stored procedure. The solution i provided here solves my issue with ease of code. CODE: Declare @sql nvarchar(max) --Note the datatype nvarchar is used since it is used for dynamic query. Declare...
As I need to send email to many people, i need to convert multiple emails into a single row delimited by semi-colon(;), i had lots of solutions, but which is an old type of solution which needs more lines of code. As i want to use one or two line code which would resolve, i found three methods for my solution which is very simple. Method 1: DECLARE @str varchar(4000) SET @str = (SELECT CONTACT_EMAIL + ';' FROM table FOR XML PATH('')) SET @str = SUBSTRING(@str,1,LEN(@str)-1) SELECT @str Method 2:...