Sorting Alphanumeric values

create table Products(productname varchar(50))

insert into Products(productname) values('Product 1')

insert into Products(productname) values('Product 11')

insert into Products(productname) values('Product 3')

insert into Products(productname) values('Product 10')

insert into Products(productname) values('Product 2')

select ProductName,CONVERT(INT,SUBSTRING(ProductName,CHARINDEX(' ' ,ProductName),LEN(ProductName)-CHARINDEX(' ' ,ProductName)+1)) as prod from Products order by prod

select productname, cast(SUBSTRING(productname,CHARINDEX(' ',productname),len(productname) - CHARINDEX(' ',productname)+1) as int) as prod from Products order by prod

Solution:

ProductName prod

-------------------------------------------------- -----------

Product 1 1

Product 2 2

Product 3 3

Product 10 10

Product 11 11

(5 row(s) affected)

Delete duplicate records from table

-- Delete Duplicate Record from Table.....

create table dup

(id int,

name varchar(10))

insert into dup values (1,'Suahs')

Insert into dup values (2,'Bijoy')

Insert into dup values (3,'Ashish')

Insert into dup values (1,'Suahs')

Insert into dup values (2,'Bijoy')

Insert into dup values (3,'Ashish')

Insert into dup values (1,'Suahs')

Insert into dup values (2,'Bijoy')

Insert into dup values (3,'Ashish')

select * from dup

1) select distinct * into #temp1 from dup

2) delete from dup

3) insert into dup select * from #temp1

4) select * from dup

---------------------------------------------------------------------

How to import an Excel file into SQL Server 2005 using Integration Services

http://blogs.techrepublic.com.com/datacenter/?p=205

How to use 2 Columns (one is StartDate another is EndDate) to compare with System Date?

create TABLE tab

(

PromCode VARCHAR(10),

Start_Date DATETIME,

End_Date DATETIME

)

INSERT

INTO tab

SELECT

'A001', '2010/05/01', '2010/05/30' UNION ALL

SELECT

'A002', '2010/04/01', '2010/04/30' UNION ALL

SELECT

'C120', '2010/03/01', '2010/03/31' UNION ALL

SELECT

'D120', '2010/04/22', '2010/04/28' UNION ALL

SELECT

'E120', '2010/03/01', '2010/03/31'

select PromCode,convert(varchar(10),Start_Date,103) as Start_Date,convert(varchar(10),End_Date,103) as End_Date,

case

when Start_Date > getdate() then 'Future'

when GETDATE() between Start_Date and End_Date then 'Process'

when getdate() not between start_Date and END_DATE then 'Expired'

when Start_Date > GETDATE() then 'Future'

when End_Date < GETDATE() then 'Expired'

end as status

from tab

Display Column Name & Data Type

select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH from information_schema.COLUMNS where TABLE_NAME = 'Device_Master'

Database – offline, Drop

alter database GSPLAsset set offline

drop database GSPLAsset

Dynamic Performance Views

select * from syscolumns

select * from systypes

Check for Existence of Table in DB

declare

@tabname varchar(100)

Select @tabname = Table_Name from INFORMATION_SCHEMA.TABLES where TABLE_NAME='TIDH'

if exists(select Table_Name from INFORMATION_SCHEMA.TABLES where TABLE_NAME='TIDH')

begin

print 'Table Exists:'+@tabname+' '

--select * from TIDH

end

else

begin

print 'Table not found in this database'

end

Procedure - Backup Database

USE [SVDP]

/****** Object: StoredProcedure [dbo].[backup_database] Script Date: 05/24/2010 17:57:21 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[backup_database]

(

@DbName VARCHAR(250),

@dbpath VARCHAR(500)

)

DECLARE @Currentdate VARCHAR(10)

DECLARE @Currenttime VARCHAR(15)

BEGIN

BEGIN TRY

SET @Currentdate =CONVERT(VARCHAR(10),GETDATE(),105)

SET @Currenttime = REPLACE(RIGHT('0'+LTRIM(RIGHT(CONVERT(varchar,getDate(),100),8)),8),':','-')

SET @dbpath = @dbpath +'\' +@Currentdate+'_'+@Currenttime+'_'+@DbName +'.' + 'bak'

PRINT @dbpath

--DBCC shrinkdatabase (@DbName,50) -- Shrink the DB - 50%

BACKUP DATABASE @DbName TO DISK = @dbpath

END TRY

BEGIN CATCH

SELECT

ERROR_NUMBER() as ErrorNumber,

ERROR_MESSAGE() as ErrorMessage

PRINT 'Backup Could not be taken for '+@DbName+''

END CATCH

END

--EXECUTE dbo.backup_database

--@DbName = 'ScadaDB', -- VARCHAR(250)

--@dbpath = 'e:\Backup' -- VARCHAR(500)

Cascade – Update/Delete

ALTER TABLE dbo.UserInUnitsSet Add CONSTRAINT fk_userid_Update FOREIGN KEY (userid) REFERENCES users(USERID) ON UPDATE CASCADE

ALTER TABLE dbo.UserInUnitsSet Add CONSTRAINT fk_userid_Delete FOREIGN KEY (userid) REFERENCES users(USERID) ON DELETE CASCADE

Run SQL Server on Command Prompt – (Command Line)

1) Install Sql Native Client

2) Go to Run -> Sqlcmd –S <ServerName> -U <UserName> -P <Password>

3) Sqlcmd Help

usage: Sqlcmd [-U login id] [-P password]

[-S server] [-H hostname] [-E trusted connection]

[-d use database name] [-l login timeout] [-t query timeout]

[-h headers] [-s colseparator] [-w screen width]

[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]

[-c cmdend] [-L[c] list servers[clean output]]

[-q "cmdline query"] [-Q "cmdline query" and exit]

[-m errorlevel] [-V severitylevel] [-W remove trailing spaces]

[-u unicode output] [-r[0|1] msgs to stderr]

[-i inputfile] [-o outputfile] [-z new password]

[-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]

[-k[1|2] remove[replace] control characters]

[-y variable length type display width]

[-Y fixed length type display width]

[-p[1] print statistics[colon format]]

[-R use client regional setting]

[-b On error batch abort]

[-v var = "value"...] [-A dedicated admin connection]

[-X[1] disable commands, startup script, enviroment variables [and exit]]

[-x disable variable substitution]

[-? show syntax summary]

To List all the databases on the server:sp_databases

To list fields in a table called foo:sp_help tablename
sp_help foo

HOW TO USE:

C:\Windows>sqlcmd -S MYSERVER\SQL2005

1> USE MyTestDB

2> SELECT Name FROM Customers

3> SELECT TOP 1 Telephone FROM Customers

4> GO

Changed database context to 'MyTestDB'.

Name

--------------------------------------------------

Joe Bloggs

Bob Marley

Jimi Hendrix

(3 rows affected)

Telephone

------------------------------

020 2999999

(1 rows affected)

1>

Convert a given value to hh:mm:ss

declare

         @sec     int

select             @sec = 64597

select             right('0' + rtrim(convert(char(2), @sec / (60 * 60))), 2) + ':' +

         right('0' + rtrim(convert(char(2), (@sec / 60) % 60)), 2) + ':' +

         right('0' + rtrim(convert(char(2), @sec % 60)),2)

Use of PATINDEX

- Logic for Next Available Employee Code

begin

Declare

@ExtractInt int,

@ExtractChar varchar(10),

@staffId varchar(10)

select @staffId =Max(EmployeeCode) from StaffMaster

if(@staffId is not null)

begin

select @ExtractChar = Substring(EmployeeCode,0,Patindex('%[0-9]%',EmployeeCode)) from StaffMaster

select @ExtractInt = Substring(EmployeeCode,Patindex('%[0-9]%',EmployeeCode),len(EmployeeCode)) from StaffMaster

set @ExtractInt = @ExtractInt + 1

set @staffId =@ExtractChar +cast(@ExtractInt as varchar)

--print 'Next Available ID:' + @staffId

select @staffId 'NextAvailableEmpCode'

end

end

Disable Constraints - Temporary

alter table BusOwnerMaster NOCheck constraint ALL

Get Current SQL Version

select @@VERSION

Stuff Function

Create Table #TempTable

(

Conductor varchar(500),Denomination varchar(100),[Assigned Block] varchar(500),

Returned varchar(500),[Route] varchar(50),Id int identity primary key

)

insert into #TempTable

Select StaffMaster.FirstName +' '+StaffMaster.LastName 'Conductor',

DenominationMaster.Denomination,

Cast(ConductorBlockAssignment.StartSerialNo as varchar) + '-'+Cast(ConductorBlockAssignment.EndSerialNo as varchar) 'Assigned Block',

COALESCE(cast(ConductorBlockAssignment.EODSerialNo as char),'Not Returned') 'Returned',RouteMaster.RouteParent 'Route'

from ConductorBlockAssignment

INNER JOIN BusMaster ON BusMaster.VehicleId = ConductorBlockAssignment.VehicleId

INNER JOIN StaffMaster ON StaffMaster.StaffId= ConductorBlockAssignment.staffId

INNER JOIN Terminal_master ON Terminal_master.Terminal_id= ConductorBlockAssignment.Terminal_id

INNER JOIN BlockSerialMaster ON BlockSerialMaster.BlockId = ConductorBlockAssignment.BlockId

INNER JOIN DenominationMaster ON DenominationMaster.Denominationid= ConductorBlockAssignment.DenominationId

INNER JOIN RouteMaster ON RouteMaster.RouteId = ConductorBlockAssignment.RouteId

where ConductorBlockAssignment.staffId = @StaffId

order by ConductorBlockAssignment.DenominationId

select [Route],Denomination,Conductor,

stuff(

(

select ','+ [Assigned Block] from #TempTable where Denomination = t.Denomination and [Route]=t.[Route]

and Conductor=t.Conductor

for XML path('')

),1,1,'') As [Assigned Block],

stuff(

(

select ','+ LTRIM(RTRIM([Returned])) from #TempTable where Denomination = t.Denomination and [Route]=t.[Route]

and Conductor=t.Conductor

for XML path('')

),1,1,'') As [Returned Block]

from

(

select distinct [Route],Denomination,Conductor from #TempTable As TT

Order By [Route]

                    drop table #TempTable

Date Formats - Sql Server

SELECT convert(datetime, 'Oct 23 2012 11:01AM', 100) -- mon dd yyyy hh:mmAM (or PM) 
SELECT convert(datetime, 'Oct 23 2012 11:01AM') -- 2012-10-23 11:01:00.000

-- Without century (yy) string date conversion - convert string to datetime function 
SELECT convert(datetime, 'Oct 23 12 11:01AM', 0) -- mon dd yy hh:mmAM (or PM) 
SELECT convert(datetime, 'Oct 23 12 11:01AM') -- 2012-10-23 11:01:00.000

-- Convert string to datetime sql - convert string to date sql - sql dates format
-- T-SQL convert string to datetime - SQL Server convert string to date 
SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy 
SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd 
SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy 
SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy 
SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy 
-- mon types are nondeterministic conversions, dependent on language setting 
SELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy 
SELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy 
-- 2016-10-23 00:00:00.000
SELECT convert(datetime, '20:10:44', 108) -- hh:mm:ss 
-- 1900-01-01 20:10:44.000

-- mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format - SQL Server datetime format 
SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109) 
-- 2016-10-23 11:02:44.013
SELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyy 
SELECT convert(datetime, '2016/10/23', 111) -- yyyy/mm/dd 
-- YYYYMMDD ISO date format works at any language setting - international standard 
SELECT convert(datetime, '20161023')
SELECT convert(datetime, '20161023', 112) -- yyyymmdd 
-- 2016-10-23 00:00:00.000
SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm 
-- 2016-10-23 11:02:07.577
SELECT convert(datetime, '20:10:25:300', 114) -- hh:mm:ss:mmm(24h) 
-- 1900-01-01 20:10:25.300
SELECT convert(datetime, '2016-10-23 20:44:11', 120) -- yyyy-mm-dd hh:mm:ss(24h) 
-- 2016-10-23 20:44:11.000
SELECT convert(datetime, '2016-10-23 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm 
-- 2016-10-23 20:44:11.500

-- Style 126 is ISO 8601 format: international standard - works with any language setting 
SELECT convert(datetime, '2008-10-23T18:52:47.513', 126) -- yyyy-mm-ddThh:mm:ss(.mmm) 
-- 2008-10-23 18:52:47.513

-- Convert DDMMYYYY format to datetime - sql server to date / datetime
SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105) 
-- 2016-01-31 00:00:00.000
-- SQL Server T-SQL string to datetime conversion without century - some exceptions
-- nondeterministic means language setting dependent such as Mar/Mär/mars/márc
SELECT convert(datetime, 'Oct 23 16 11:02:44AM') -- Default 
SELECT convert(datetime, '10/23/16', 1) -- mm/dd/yy U.S. 
SELECT convert(datetime, '16.10.23', 2) -- yy.mm.dd ANSI
SELECT convert(datetime, '23/10/16', 3) -- dd/mm/yy UK/FR 
SELECT convert(datetime, '23.10.16', 4) -- dd.mm.yy German 
SELECT convert(datetime, '23-10-16', 5) -- dd-mm-yy Italian 
SELECT convert(datetime, '23 OCT 16', 6) -- dd mon yy non-det. 
SELECT convert(datetime, 'Oct 23, 16', 7) -- mon dd, yy non-det. 
SELECT convert(datetime, '20:10:44', 8) -- hh:mm:ss 
SELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9) -- Default with msec 
SELECT convert(datetime, '10-23-16', 10) -- mm-dd-yy U.S. 
SELECT convert(datetime, '16/10/23', 11) -- yy/mm/dd Japan 
SELECT convert(datetime, '161023', 12) -- yymmdd ISO 
SELECT convert(datetime, '23 Oct 16 11:02:07:577', 13) -- dd mon yy hh:mm:ss:mmm EU dflt 
SELECT convert(datetime, '20:10:25:300', 14) -- hh:mm:ss:mmm(24h) 
SELECT convert(datetime, '2016-10-23 20:44:11',20) -- yyyy-mm-dd hh:mm:ss(24h) ODBC can. 
SELECT convert(datetime, '2016-10-23 20:44:11.500', 21)-- yyyy-mm-dd hh:mm:ss.mmm ODBC

Oracle Applications Knowledge Repository

Tuesday, October 26, 2010

SQL Server Tips and Tricks

How to use 2 Columns (one is StartDate another is EndDate) to compare with System Date?

Run SQL Server on Command Prompt – (Command Line)

Convert a given value to hh:mm:ss

No comments:

Post a Comment