Ayo Softech

SQL

 For more Query and example
http://allenbrowne.com/subquery-01.html
http://www.java2s.com/Code/Oracle/CatalogOracle.htm
Example
@Download SQL server 2008
1.To replace the existing dataset
2.Show how to convert a Table Data in to XML format in SQL Server?
3.Show practically using SQL employee’s age greater than 30?
4. back-up SQL Server Database and Restore Database?
5.How will you obtain names of employees with top 10 salaries?
6.SQL query to merge two different columns into single column?
7.SQL CASE syntax capability? 
8.Obtain second highest salary from the table?
9.Creating a Primary Key on an Existing Table
10.How to increase field size
11.Display structure of table
12.List All Tables of Database
13.List All fields of Database with Tablewise 14.Select Query
15.Sub Query 
16.Update Date
17.Last_user_update 
18.Create/calling Procedure
19.Recently Executed T-SQL Query
20. Create a new column in a view and assign it a data type
21.How to Setup Automatic Daily Database Backups in SQL Server 2008
22.How to Find and Remove Duplicate Column Records in a Table Row (SQL Server)
23.Quick Create Table
24.Delete All Foreign Keys in a Database 
25.Fastest LeapYear checker
26.print 1 to 100
27.find specific Field name in Add DB 

28.SORT STRINGS in SQL Server

29.Sorting in SQL SERVER varchar types
30.Select records 51 to 80 in SQL SERVER?
31.Extract the last string
32.Split function in SQL SERVER
 
 



QUERY:
1.DBCC showconfig('CustomerTable');-please show in web gallary

=>Below diagram represent the steps of execution plan.

 

1.To replace the existing dataset

  1. In the Report Data pane, right-click the dataset AdventureWorksDataset, and then click Dataset Properties.
    Not
    If you do not see the Report Data pane, from the View menu, click Report Data.
  2. In Data source, verify that AdventureWorks_Ref is selected.
  3. In Query type, verify that Text is selected.
  4. Click the Query Designer button to open the query designer.
  5. Replace the text with the following query into the text box.
    SELECT 
   soh.OrderDate AS [Date], 
   soh.SalesOrderNumber AS [Order], 
   pps.Name AS Subcat, pp.Name as Product,  
   SUM(sd.OrderQty) AS Qty,
   SUM(sd.LineTotal) AS LineTotal
FROM Sales.SalesPerson sp 
   INNER JOIN Sales.SalesOrderHeader AS soh 
      ON sp.BusinessEntityID = soh.SalesPersonID
   INNER JOIN Sales.SalesOrderDetail AS sd 
      ON sd.SalesOrderID = soh.SalesOrderID
   INNER JOIN Production.Product AS pp 
      ON sd.ProductID = pp.ProductID
   INNER JOIN Production.ProductSubcategory AS pps 
      ON pp.ProductSubcategoryID = pps.ProductSubcategoryID
   INNER JOIN Production.ProductCategory AS ppc 
      ON ppc.ProductCategoryID = pps.ProductCategoryID
GROUP BY ppc.Name, soh.OrderDate, soh.SalesOrderNumber, 
   pps.Name, pp.Name,    soh.SalesPersonID
HAVING (ppc.Name = 'Clothing' 
   AND (soh.OrderDate BETWEEN (@StartDate) AND (@EndDate)))
    This is the same query as before, except that a condition with two limiting parameters has been added:
    AND (soh.OrderDate BETWEEN (@StartDate) AND (@EndDate))
  6. Click Run (!) on the toolbar. The Define Query Parameters dialog box opens to prompt you for parameter values.
  7. Provide two values to see a filtered result set:
    1. In the Parameter Value column, enter a value for @StartDate, for example, 1/31/2001.
    2. In the Parameter Value column, enter a value for @EndDate, for example, 1/31/2003.
  8. Click OK. 
  9. The result set displays a filtered dataset for orders in the years 2001 and 2002.
  10. Click OK twice. The Report Data pane is populated with the dataset fields. Also note that two report parameters, StartDate and EndDate, are automatically created and appear under the Parameters node.
After you have defined query parameters for the report, you have to change the data type of the report parameters to match the data type of the source data. The default is Text, which maps to the String data type in most data sources. If a source data is numeric, Boolean, or Date/Time, you have to change the report parameter data type.

2.Show how to convert a Table Data in to XML format in SQL Server?

Syntax: – Select * From [TableName] For XML Path
So, let demonstrate a sweet and simple example to see how exactly we can convert able data in to XML format using a simple query.
Assume that we have the following Customer’s table with their respective data types and data.


Now, let’s create a simple query to obtain your required result.
Query: -
Select * from Customer For XML Path
As soon as you execute the above query you will see the result like below diagram.

Now, when you click on the link you will see the XML result like below
diagram.

In the above diagram you can clearly see that now your table data has been successfully converted in to the XML format.

 

3.Show practically using SQL employee’s age greater than 30?

select Employees.EmployeeName, DATEDIFF(YEAR,EmployeeDOB,getdate())as Age
from Employees where DATEDIFF(YEAR,EmployeeDOB,getdate())>= 30

4. back-up SQL Server Database and Restore Database?

There are basically two ways (methods) in order to back-up SQL Server Database and Restore database.
The following are the two methods to do the same.
1. By simple Detach and Attach Methods.
2. By creating a .bak file of the Database.
Let’s see a simple example on both the above methods for creating back-up and restoring database.
Method1: -By simple Detach and Attach.
Let us assume that we have a Customer Database on which we have to perform the Back-up and Restore action.
In order to perform simple Detach and Attach method just right click on the database name > go to Tasks > Select Detach. A new window will pop-up from the check box of drop box just select the drop box and clicks ok.


As soon as you click on ok you find that now the Customer database is detach from the Databases.

Now, just browse to the location of “Customer.mdf” database and copy the both “Customer.mdf” and “Customer_log.ldf” and save both the file to your specified location.

Now, let’s see how we can restore the database back to the Databases.
In order to restore “Customer” database right click on Databases > Select Attach a new will pop-up like below diagram from that click on Add then again a new window will open of locate database file from that select “Customer.mdf” and click ok again you will be revert back to the Attach database window from that click ok.




Now, you will see that “Customer” database is again attached to the Databases.

Method2: -By creating a .bak file of the Database.
In order to create a .bak file right click on “Customer” database > go to Tasks > Select back up.

As soon as you select back up a new window like below diagram.

Now click on ok, a message will be displayed like below diagram.

Now, just browse to the destination, you will see a backup file (.bakformat) file is created.

Now, let’s see how to restore back the database.

As soon as you click on Restore Database a new window will appear like below diagram from that select from device and click [----] then again a new window will appear of specify back-up from that click on add.


As soon as you click on add then again a new window will appear like below diagram of Locate back-up file from that select the Database on which you have created the back-up and then click ok.

Now just click on ok.

Similarly, when you click on ok you will revert back to the restore database window from that select the database name and check the restore box as marked and later click on ok.

If everything goes right you will see the message as completed like below diagram.
 

5.How will you obtain names of employees with top 10 salaries?
EmpId EmpName EmpSalary
1 Alok 20000
2 Ashok 30000
3 Anand 25000
4 Akash 18000
5 Prasad 33000
6 Manoj 30000
7 Sachin 40000
8 Denish 36000
9 Rajesh 24000
10 Hari 4000
11 Ravi 25000
12 Kiran 20000
13 Shobha 30000
14 Pallavi 43000
15 Reshma 41000
Query:- 
select Employee.EmpName,Employee.EmpSalary from Employee where Employee.EmpSalary in
(select distinct top(10)Employee.EmpSalary from Employee order by Employee.EmpSalary desc)
OUTPUT:-
EmpName EmpSalary
Alok 20000
Ashok 30000
Anand 25000
Akash 18000
Prasad 33000
Manoj 30000
Sachin 40000
Denish 36000
Rajesh 24000
Ravi 25000
Kiran 20000
Pallavi 43000
Reshma 41000

6.SQL query to merge two different columns into single column?

Answer:
let’s us assume that we have the following table of Employee.
Emp_Id Emp_Name Emp_Salary_2010 Emp_Salary_2011
1 Shiv 17000 19000
2 Raju 13500 15000
3 Sham 15000 18000
4 Moosa 11000 14000
5 Feroz 12000 16000
Now we want to merge the Emp_Salary_2010 and Emp_Salary_2011 columns into a single column as Salary.
Query:-
select Emp_Name,Emp_Salary_2010 as Salary from Employee
union
select Emp_Name,Emp_Salary_2011 as Salary from Employee
Output:-
Emp_Name Salary
Shiv 17000
Shiv 19000
Raju 13500
Raju 15000
Sham 15000
Sham 18000
Moosa 11000
Moosa 14000
Feroz 12000
Feroz 15000

7.SQL CASE syntax capability?

Answer:
Let’s us assume that we have the following table of Employee.
Emp_Id
Emp_Name
Emp_Salary
1
Shiv
17000
2
Raju
13500
3
Sham
15000
4
Moosa
11000
5
Firoz
12000
There can be a scenario we have to display employee name whose salary is greater than “some amount” or less than “some amount” for that purpose we use case statement.
Let’s us consider that we have to display all the employee names from the employee table and the status like salary is greater than 13000 or lesser than 13000.
Query:
 SELECT Emp_Name,CASE
 when (Emp_Salary>13000) then 'Greater than 13000'
 else 'Lesser than 13000'
 end as Status
 FROM Employee
Output:
Emp_Name
Status
Shiv
Greater
than 13000
Raju
Greater
than 13000
Sham
Greater
than 13000
Moosa
Lesser
than 13000
Firoz
Lesser
than 13000

8.Obtain second highest salary from the table?

Let’s us assume that we have the following table of Employee.
mp_Id Emp_Name Emp_Salary
1 Shiv 17000
2 Raju 13500
3 Sham 15000
4 Moosa 11000
5 Feroz 12000
Now we want to find out second highest salary from Employee table, as you see that the second highest salary is 15000 and we want to display the same.
Query:-
SELECT Emp_Name,Emp_Salary
FROM Employee e1
WHERE
2 = (SELECT COUNT(DISTINCT (e2.Emp_Salary))FROM Employee e2 WHERE e2.Emp_Salary >= e1.Emp_Salary)
The above employee table contains two same records as second highest salary so the result set will look like below output table.
Output:-
Emp_Name Emp_Salary
Sham 15000


9.Creating a Primary Key on an Existing Table
1> create table employee(
2>     ID          int,
3>     name        nvarchar (10),
4>     salary      int,
5>     start_date  datetime,
6>     city        nvarchar (10),
7>     region      char (1))
8> GO
1>
2> insert into employee (ID, name,    salary, start_date, city,       region)
3>               values (1,  'Jason', 40420,  '02/01/94', 'New York', 'W')
4> GO


(1 rows affected)
1>
2> select * from employee
3> GO
ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
          1 Jason            40420 1994-02-01 00:00:00.000 New York   W


(9 rows affected)
1>
2> -- Creating a Primary Key on an Existing Table
3>
4>
5> ALTER TABLE Employee
6>   ADD CONSTRAINT PK_EmployeeID
7>   PRIMARY KEY (ID)
8>
9> drop table employee
10> GO
Msg 8111, Level 16, State 1, Server JAVA2S\SQLEXPRESS, Line 5
Cannot define PRIMARY KEY constraint on nullable column in table 'Employee'.
Msg 1750, Level 16, State 1, Server JAVA2S\SQLEXPRESS, Line 5
Could not create constraint. See previous errors.
1>

10.How to increase field size
ALTER TABLE test ALTER COLUMN [Id] varchar(50)

11.Display structure of table
exec sp_columns tableName
exec sp_help tableName
 
12.List All Tables of Database
SELECT*FROM sys.Tables

13.List All fields of Database with Tablewise
select C.Name,T.Name from sys.columns C join sys.tables T on C.object_id=T.object_id order by T.Name
14.Select Query
1) Select * from tblTest (Returns all columns/Rows)

2) Select * from tblTest Where TestID=2 (Returns the row/s which TestID has value 2)

3) Select * from tblTest where TestID Between 10 and 20 (Return all rows between 10 and 20, this result includes 10 and 20)

4) Select * from tblTest Where TestCity in ('New York','Washington','California') (Returns all rows which city is NewYork, Washington, california)

5) Select * from tblTest Where TestName Like 'A%' (Return all rows where the name starts letter A)

6) Select * from tblTest Where TestName Like '%A' (Return all rows where the name ends letter A)

7) Select * from tblTest Where TestName Like '[ABC]%' (Return all rows of name start with A / B / C)

8) Select * from tblTest Where TestName Like '[^ABC]%' (Return all rows of name not start with A and B and C)

9) Select (TestName+space(1)+TestCity) as Address from tblTest (Returns single column address, name and city added together with a space)

10) Select * from tblTest Where TestName IS NULL (Return all rows which TestNane has null values)

11) Select * from tblTest Where TestName IS NOT NULL (Return all rows which TestNane has not null values)

12) Select * from tblTest Order By TestID Desc (Sort the result set descending order, Asc or not using any sort Ascending order)

13) Select 'Visual Studio' as IDE, '2010' as Version (Creating memory resident result set with two columns[IDE and Version])

14) Select Distinct TestID from tblTest (Returns unique rows based on TestID)

15) Select Top 10 * from tblTest  (Return 10 customers randomly)

16) Select getdate() (Shows the current date)

17) Select db_name() (shows the database name which you are working on)

18) Select @@Servername (Shows name of the server)

19) Select serverproperty ('Edition') (You can pass following ServerName, Edition, EngineEdition, ProductLevel to get current information about the server)

20) Select user_name() (Get current user)

21)  Select *  into #test from tblTest  (Create temporary table #test and insert all records from tblTest)

22)  Select Max(TestID) from tblTest (Returns Maximum TestID from tblTest)

23)  Select * from tblTest Compute Max(TestID) (Returns two result sets - getting all rows and maximum value of TestID)

24) Select FirstName, LastName,  Salary, DOB,
  Case Gender
    When 'M' Then 'Male'
    When 'F' Then 'Female'
  End
From Employees
 
15.Sub Query
SQL> select * from Employee
  2  /

ID   FIRST_NAME LAST_NAME  START_DATE  END_DATE        SALARY CITY       DESCRIPTION
---- ---------- ---------- ----------- ----------- ---------- ---------- ---------------
01   Jason      Martin     1996-JUL-25 2006-JUL-25    1234.56 Toronto    Programmer
02   Alison     Mathews    1976-MAR-21 1986-FEB-21    6661.78 Vancouver  Tester
03   James      Smith      1978-DEC-12 1990-MAR-15    6544.78 Vancouver  Tester
04   Celia      Rice       1982-OCT-24 1999-APR-21    2344.78 Vancouver  Manager
05   Robert     Black      1984-JAN-15 1998-AUG-08    2334.78 Vancouver  Tester
06   Linda      Green      1987-JUL-30 1996-JAN-04    4322.78 New York   Tester
07   David      Larry      1990-DEC-31 1998-FEB-12    7897.78 New York   Manager
08   James      Cat        1996-SEP-17 2002-APR-15    1232.78 Vancouver  Tester

8 rows selected.

SQL>
SQL>
SQL>
SQL> -- Writing Nested Subqueries
SQL>
SQL> SELECT city, AVG(salary)
  2  FROM employee
  3  GROUP BY city
  4  HAVING AVG(salary) <
  5    (SELECT MAX(AVG(salary)) FROM employee WHERE id IN  (SELECT id FROM employee)
  6     GROUP BY city);

CITY       AVG(SALARY)
---------- -----------
Toronto        1234.56
Vancouver      3823.78

16.Update Date
update Menu_Display_Items_List_Table set Display_Untill_Date='2012/03/31'
or
DECLARE @dt datetime
SET @dt = '2012-12-31T12:05:50'
SELECT DATEADD(month, 3 - month(@dt), @dt)

17.Last_user_update
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'AdventureWorks')
AND OBJECT_ID=OBJECT_ID('table name’)


18.creating and calling stored procedure in .net:create procedure insert1(@userid int,@username varchar(50))
as
begin
insert into emp(id,name)values(@userid,@username)
end
NOTE:(id,name) are the columns of table in which you want to insert data.
CALLING STORED PROCEDURE :
initialize this globally:
public partial class1:class
{
sqlconnection con;
sqlcommand cmd;
}
on page_load(): // write on page load()
{
con=new sqlconnection(“Connection string”);
cmd=new sqlcommand();
cmd.connection=con;
cmd.commandtype=commandtype.stored procedure;
}
on button_click() // write on insert button_clik event
{
cmd.parameters.clear();
cmd.commandtext=”insert1″;
// stored procedure name just created.
cmd.parameters.addwithvalue(“@userid”,textbox1.text);
cmd.parameters.addwithvalue(“@username”,textbox2.text);
con.open();
cmd.executenonquery();
messagebox.show(“stored procedure inserted…”);
con.close();
}
19.Recently Executed T-SQL Query
SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

20. Create a new column in a view and assign it a data type

CREATE VIEW VIEW2
AS SELECT [Column1],
(NULL) AS column2 VARCHAR(10)
FROM VIEW1;
I get an error in the third line.
If I leave the query like this, without specifying the data type for Column2, the query works, but the default data type will be int, whereas I want to have VARCHAR. 
CREATE VIEW VIEW2
AS SELECT [Column1],
(NULL) AS column2 
FROM VIEW1;


21.How to Setup Automatic Daily Database Backups in SQL Server 2008
Are you backing up your SQL Server 2008 databases daily?  You should be, especially if you don't want to lose any of your precious data that you're storing.  It's incredibly easy to setup a maintenance plan in SQL Server 2008 to automatically back up one or more databases. To get started backing up your databases using SQL Server Management Studio follow the steps below.
Here's how to setup automatic daily backups for SQL Server 2008 databases:
  1. Open Microsoft SQL Server Management Studio.
  2. Expand the database server.
  3. Expand the Management folder.
  4. Right-click Maintenance Plans and select Maintenance Plan Wizard. This launches the wizard and you can now step through and create a plan customized to meet your maintenance requirements.
  5. Click 'Next' button, then name your Maintenance Plan and give description.  Select the radio button that says "Single schedule for the entire plan or no schedule".
  6. Under Schedule,  click on the 'Change' button.  This brings up the Job Schedule Properties form. In the Frequency section, change Occurs to 'Daily', and then click 'OK.
  7. Click 'Next' button, then check the box next to "Back Up Database (Full)", then click 'Next'.
  8. Click 'Next' button again, then select one or more of your Databases to be backed up using the Database(s) drop down box. Make any additional settings and then click 'Next'.
  9. On the Select Report Options form, click the 'Next' button once again.
  10. Then click the 'Finish' button to complete the wizard.
  11. The Maintenance Plan Wizard will run and should complete successfully.  Click the 'Close' button.
  12. You should now see the database backup maintenance plan you just created underneath the Maintenance Plans folder in SQL Server Management Studio.
22.How to Find and Remove Duplicate Column Records in a Table Row (SQL Server)
Here's a couple quick SQL database tips for finding and deleting duplicate values in a SQL Server table.

To find duplicates in a Column use the following SQL:
SELECT ColName1, COUNT(*) TotalCount
FROM TableName
GROUP BY ColName1 HAVING (COUNT(ColName1) > 1)
Note: Using COUNT(*) to find duplicate rows allows the query to find duplicates if ColName1 excepts NULL values.

To find duplicates in a Column and return all columns from the Table use the follow SQL:
SELECT        t1.*
FROM            TableName AS t1
WHERE        (ColumnName1 IN
            (SELECT        ColumnName1
            FROM            TableName AS t2
            GROUP BY ColumnName1
            HAVING         (COUNT(ColumnName1) > 1)))
--------------------------------------------------------------
To get Total Duplicates Use the following SQL:
SELECT COUNT(*) as TotalDuplicates FROM
(
SELECT ColName1, COUNT(*) TotalCount
FROM TableName
GROUP BY ColName1
HAVING COUNT(*) > 1
) as t
--------------------------------------------------------------
To Delete Dupicate Records or Rows use the followng SQL:
DELETE
 FROM TableName
 WHERE ID NOT IN
 (
 SELECT MAX(ID)
 FROM TableName
 GROUP BY DuplicateColumn1, DuplicateColumn2
)
Note: To use the SQL code above the table must have an identity column. The Identity Column in the example above is named "ID" and is used to identify the duplicate records.

23.Quick Create Table  
USE [DB name]
GO
/****** Object:  Table [dbo].[Table_Name]    Script Date: 12/05/2012 14:28:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[vehicle_stock](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [dealer_loc] [varchar](50) NULL,
    [model] [varchar](50) NULL,
    [fuel] [varchar](50) NULL,
    [variant_desc] [varchar](50) NULL,
    [variant_code] [varchar](50) NULL,
    [colour] [varchar](50) NULL,
    [colour_code] [varchar](50) NULL,
 CONSTRAINT [PK_Table_Name] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO 
24.Delete All Foreign Keys in a Database 

while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))
begin
    declare @sql nvarchar(2000)
    SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
    + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
    FROM information_schema.table_constraints
    WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
    exec (@sql)
end

select 'Drop table  ' + name from sys.tables

25.Fastest LeapYear checker
CREATE FUNCTION Is_Leap_Year(@Year int)
RETURNS bit
AS
BEGIN
RETURN CASE
WHEN @Year % 400 = 0
OR ( @Year % 4 = 0
AND @Year % 100 <> 0) THEN 1
ELSE 0
END;
END; 

OR

CREATE FUNCTION dbo.Is_Leap_Year(@Year int)
RETURNS bit
AS
BEGIN
RETURN ISDATE (@year * 10000 + 229)
END;

26.print 1 to 100 
with im as
(select 1 as [nom]
    union all
 select [nom] + 1 from Im where [nom] <100)
select [nom],'yogesh' from im

27.find specific Field name in Add DB 
Use  Master
Go
Declare @WhereIs Table
(
DBName Varchar(100),
ObjectName Varchar(150),
ObjectType Varchar(150)
)
Insert @WhereIs
Exec sp_msforeachdb 'use [?]; select DB_NAME(), name, Type_desc from sys.objects where name like ''%Flatmate%'''
Select * from @WhereIs



28.SORT STRINGS in SQL Server


-- Description:Given an input like "14,12,678,234,1,23"

To sort the strings and the result will be
"1,12,14,23,234,678"
-- By: Developer142
--
-- Inputs:"14,12,678,234,1,23"
--
-- Returns:Sorted string "1,12,14,23,234,678"
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1301&lngWId=5--for details.--**************************************

The steps are:
1) Use a table valued split function to split the comma delimited strings
2) Then sort the table values
3) Loop thru each values of the table and make a customised delimited
string .
The above steps are depicted below
STEP 1:
The split function goes like this
 -- Created by NILADRI BISWAS
ALTER FUNCTION [dbo].[fnSplit]
(@oldstring as varchar(100),@delimeter as varchar(1))
RETURNS @mytab table(counter int,stringval varchar(100))
AS
Begin
              
               Declare @newstring as varchar(100)
               Declare @pos as int
               Declare @i as int
               Declare @c as int     
               set @newstring = '';          
               set @i = 1
               set @c = 0
               set @pos = CHARINDEX(@delimeter, @oldstring)
               WHILE (@i != 0)
                       Begin
                               set @c = @c +1
                               insert into @mytab(counter,stringval) values(@c,@newstring + Substring(@oldstring,0, @pos))
                              
                               set @oldstring = Substring(@oldstring,@pos+1,len(@oldstring))
                               set @pos = CHARINDEX(@delimeter, @oldstring)
                               set @i = @pos;
                               if (@i = 0)
Begin
set @i = 0;
                                              set @c = @c +1
                                      insert into @mytab(counter,stringval) values(@c,@newstring + @oldstring)
                                     
End
                       End
                       return
End
Create a stored proc like this
-- Created by NILADRI BISWAS
ALTER PROCEDURE dbo.SortedList
        -- Add the parameters for the stored procedure here
        (@DelimitedValues AS VARCHAR(50))
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
        DECLARE @OUTPUT VARCHAR(30)
        DECLARE @TEMPOUTPUT VARCHAR(30)
        SET @OUTPUT = ''
        SET @TEMPOUTPUT = ''
        DECLARE @MYCURSOR CURSOR
        SET @MYCURSOR = CURSOR FOR
STEP 2:
SELECT STRINGVAL FROM dbo.fnSplit(@DelimitedValues,',')
        ORDER BY RIGHT(REPLICATE('0', 11) + stringval, 10)
STEP 3:
OPEN @MYCURSOR
        FETCH NEXT
        FROM @MYCURSOR INTO @TEMPOUTPUT
        WHILE @@FETCH_STATUS = 0
        BEGIN
        SET @OUTPUT = @OUTPUT + @TEMPOUTPUT + ','
        FETCH NEXT
        FROM @MYCURSOR INTO @TEMPOUTPUT
        END
        CLOSE @MYCURSOR
        DEALLOCATE @MYCURSOR
SET @OUTPUT    = SUBSTRING(@OUTPUT,0,LEN(@OUTPUT))
        PRINT @OUTPUT 
END

29.Sorting in SQL SERVER varchar types
-- Description:To perform sorting in SQL SERVER varchar types
-- By: Developer142
--
-- Inputs:I have a table say tblSwap with a column StroreVals.
StroreVals
20
21
29
11
10
5
7
I want to get the result in descending order using SQL SERVER 2005.
Also, if it is like "Abc", "Def","Azy","kly"?
--
-- Returns:Sorted list
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1302&lngWId=5--for details.--**************************************

FOR ALL KIND OF STRINGS
-------------------------------------------------
SELECT StroreVals
FROM tblSwap
order by right(replicate('0', 11) + StroreVals, 10)
ASSUME THAT THE STRINGS CAN BE CONVERTED INTO INTEGERS
------------------------------------------------
SELECT StroreVals
FROM tblSwap ORDER BY convert(int,StroreVals) DESC







30.Select records 51 to 80 in SQL SERVER?
-- Description:To select records from m to n in SQL SERVER 2005 +.
e.g. To fetch records from 51st to 80th
-- By: Developer142
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1304&lngWId=5--for details.--**************************************

Use ROW_NUMBER() function.
SELECT A.RECORDS FROM (SELECT ROW_NUMBER() OVER (ORDER BY RECORDS) AS ROWID,RECORDS FROM CHOOSERECORDS) A
WHERE A.ROWID BETWEEN 51 AND 80



31.Extract the last string
-- Description:To extract the last strings from "A1, B1, ABC"
-- By: Developer142
--
-- Inputs:"A1, B1, ABC"
--
-- Returns:ABC
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1305&lngWId=5--for details.--**************************************

32.Split function in SQL SERVER
-- Description:To simulate a split function in SQL SERVER.
It's a table valued function
-- By: Developer142
--
-- Inputs:Any delimited strings
e.g. 'a,b,c' and the delimeter ','
--
-- Returns:a
b
c
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1307&lngWId=5--for details.--**************************************

ALTER FUNCTION [dbo].[fnSplit]
(@oldstring as varchar(100),@delimeter as varchar(1))
RETURNS @mytab table(counter int,stringval varchar(100))
AS
Begin
              
               Declare @newstring as varchar(100)
               Declare @pos as int
               Declare @i as int
               Declare @c as int     
               set @newstring = '';          
               set @i = 1
               set @c = 0
               set @pos = CHARINDEX(@delimeter, @oldstring)
               WHILE (@i != 0)
                       Begin
                               set @c = @c +1
                               insert into @mytab(counter,stringval) values(@c,@newstring + Substring(@oldstring,0, @pos))
                              
                               set @oldstring = Substring(@oldstring,@pos+1,len(@oldstring))
                               set @pos = CHARINDEX(@delimeter, @oldstring)
                               set @i = @pos;
                               if (@i = 0)
Begin
set @i = 0;
                                              set @c = @c +1
                                      insert into @mytab(counter,stringval) values(@c,@newstring + @oldstring)
                                     
End
                       End
                       return
End

SELECT SUBSTRING('A1, B1, ABC',LEN('A1, B1, ABC')-CHARINDEX(',','A1, B1, ABC')+1,LEN('A1, B1, ABC'))
 

 
 

1 comment:

Subscribe to: Posts (Atom)