Finding number of occurrence of a character in SQL

Filed in SQL Leave a comment

declare @myvar varchar(20)
set @myvar = ’H—el-lo -Wo-r-l-d’
select len(@myvar) - len(replace(@myvar,’-',”))
Result: 8

SQL Server 2008 R2 only Service Pack 1

Filed in SQL Leave a comment

Alternatively with the windows updates you can update your plain R2

Related Link

INSERT INTO Table by selecting similar table with selected columns

Filed in SQL Leave a comment

INSERT INTO Item (ItemID, ItemName, ItemDescription, Manufacturer, Authentication_Info, NDC, DEA_Schedule, PedigreeItem, SaleDescription, ItemCost, ItemPrice, AWP, CF_Size)

SELECT ItemID, ItemName, ItemDescription, Manufacturer, Authentication_Info, NDC, DEA_Schedule, PedigreeItem, SaleDescription, ItemCost, ItemPrice, AWP, CF_Size from ParagonDB.dbo.TblItem

WHERE CatID IN(1,2,3,4,5,6,7,8,9,10,11,12) AND SubCatID IN(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17) AND SVPG IS NULL

Create New code templates in SQL Server

Filed in SQL Leave a comment

In Management Studio in Menu > Click View > Template Explorer > In this explorer you see different templates. Right click in the Template Explorer > New > Template. It create a new template file, name your template and then right click that template file and click edit. Now you make you new template and save it. That’s it!

Now whenever you click on this new template it create a new Query window with the same code which you write on the template.

Physically file are located on the below path:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql

Update statement while select data from same table

Filed in SQL Leave a comment

UPDATE EmailEvents
SET SubjectES = (SELECT ee.SubjectES FROM EmailEvents ee WHERE ee.Clientid IS NULL AND ee.EmailEventId = EmailEvents.EmailEventId)
WHERE EmailEvents.Clientid = ‘OE’

Key point; you cannot define table alias after Update clause so you have to use complete table name when referring main table columns.

One more example of Self join.
select EmailEventid, Body,clientid,
(SELECT ee.Body FROM EmailEvents ee WHERE ee.Clientid IS NULL AND ee.EmailEventId = EmailEvents.EmailEventId AND ee.Body = EmailEvents.Body )
from EmailEvents
order by emaileventid, clientid

,

Short-Circuiting in SQL

Filed in SQL Leave a comment

I found a very cialis cheap good article on “Short-Circuiting in SQL” at the below link, but unfortunately you need a userid and password to view the post. I am copy pasting a useful thing from there.

http://www.sqlservercentral.com/articles/T-SQL/71950/

SELECT TOP 5 *
FROM #Test
WHERE ISNUMERIC(b)= 1 AND b < 10

In column b there is some alpha numeric and numeric data

,

Convert column into rows in SQL 2005 using Pivot/Unpivot

Filed in SQL Leave a comment

Before

ID

Quantity 1

Quantity 2

Quantity 3

145

5

14

3

146

12

13

14

148

44

2323

NULL

SELECT *

FROM

(SELECT RFQDetID, Quantity1, Quantity2, Quantity3

FROM RFQDetail where RFQID = 230) p

UNPIVOT

(Value FOR Old_Col_Header IN

(Quantity1, Quantity2, Quantity3)

)AS unpvt;

GO

After applying unpivot data look like

ID

Value

Old_Col_Header

145

5

Quantity1

145

14

Quantity2

145

3

Quantity3

146

12

Quantity1

146

13

Quantity2

146

14

Quantity3

148

44

Quantity1

148

2323

Quantity2


Specifying values for In clause in SP from C#

Filed in .Net | SQL Leave a comment

select  * from pk where charindex(‘,’+cast(pkid as varchar(50))+’ ‘,’,12 ,13 ‘,1) > 0

But I think i would be slow if you have large data

Inserting data into a duplicate table, without entering column names

Filed in SQL Leave a comment

INSERT INTO DeletedPK SELECT * FROM PK WHERE PKId = 33

Creating a table by coping old table structure copy in SQL 2005

Filed in SQL Leave a comment

E.g 1

SELECT * INTO NewTblName FROM ExistingTblName WHERE 1 = 0

E.g 2

SELECT * INTO NewTblName FROM

(SELECT PKId, PKStatusId, P.CreatedDate, P.LastModifiedDate, PayOptId, AccountNo, RoutingNo, Pin, DedOfferAmount,  IntTrackNo, Lang, RequestUrl, PCode, PromoCodeAmount, S.[SId], FirstName, LastName, Address1, Address2, Address3, City, [State], ZipCode, Country, Phone, Email, [Password]  FROM Sel S, Pro P WHERE 1 = 0 ) AB

TOP