Archive for August 2015

Regular Expression – II   Leave a comment

//Matches string start with pattern
String strData = “Hello there”;
Regex rg = new Regex(“^Hello”);
bool truefalse = rg.IsMatch(strData);

rg = new Regex(“^Hola”);
truefalse = rg.IsMatch(strData);

//Matches strung end with pattern
strData = “Hello there”;
rg = new Regex(“there$”);
truefalse = rg.IsMatch(strData);

//Matches char from the list
rg = new Regex(“[aeiou]”);
truefalse = rg.IsMatch(strData);

//Not matches char from the list
rg = new Regex(“[^aeiou]”);
truefalse = rg.IsMatch(strData);

//Matching range
strData = “Hello there, you are 8 years old.”;
rg = new Regex(“[0-9]”);
truefalse = rg.IsMatch(strData);

strData = “55”;
rg = new Regex(“[0-9][0-5]”);
truefalse = rg.IsMatch(strData);

//Match string contains A-Z, a-z and 0-9
strData = “Hello”;
rg = new Regex(“[^a-zA-Z0-9]”);
truefalse = rg.IsMatch(strData);

//Match string contains A-Z, a-z, 0-9 with white space, below code return false when string contains chars other than defined.
strData = “hello there 0″;
rg = new Regex(@”[^a-zA-Z0-9\s]”);
truefalse = rg.IsMatch(strData);

//Matches a-z and A-Z
strData = “hello there”;
rg = new Regex(@”[^\w\s]”);
truefalse = rg.IsMatch(strData);

//Matches a-z, A-Z and 0-9
strData = “hello there 0″;
rg = new Regex(@”[^\w\d\s]”);
truefalse = rg.IsMatch(strData);

//Note*: \w represet a-zA-Z, \W other than word characters and \d represent 0-9, \D other than non-decimal
//\s represent whitespace

//To match the number of characters that matches certain pattern.

//True scenario
strData = “12345”;
rg = new Regex(@”\d{5}”);
truefalse = rg.IsMatch(strData);

strData = “12345-6789″;
rg = new Regex(@”\d{5}-\d{4}”);
truefalse = rg.IsMatch(strData);

//False scenario
strData = “12345-678″;
rg = new Regex(@”\d{5}-\d{4}”);
truefalse = rg.IsMatch(strData);

strData = “12345-678A”;
rg = new Regex(@”\d{5}-\d{4}”);
truefalse = rg.IsMatch(strData);

//Minimum and Maximum Occurences
//True scenario
strData = “123”;
rg = new Regex(@”\d{1,3}”);
truefalse = rg.IsMatch(strData);

strData = “12”;
rg = new Regex(@”\d{1,3}”);
truefalse = rg.IsMatch(strData);

//False scenario – string end with numeric
strData = “A124B”;
rg = new Regex(@”\d{1,3}$”);
truefalse = rg.IsMatch(strData);

#regular-expression

Posted August 23, 2015 by Izharuddin Shaikh in General

Tagged with

SQL Server Basic – VII   Leave a comment

–Joining two tables without a clause i.e, cross products for two tables

USE Pubs
SELECT * from authors,titleauthor
GO
SELECT * from authors cross join titleauthor
GO

This query is not actually joined on any common clause. It is a cartesian product, so if there are 20 records in first table
and 30 records in the second we will get a total of 600 records.
Not putting a where clause is same as a CROSS JOIN.

–Using cross products to generate unique derived fields

USE Pubs
SELECT distinct (stor_id + '-' + au_lname) compfld
from stores cross join authors
order by compfld

‘+’ operators concatenates two fields for composite field generation Primary key of two different tables can be used to create
a composite key in a link table this way.

–Using Inner Joins to find exact matching rows

SELECT au_id,pubdate
from titleauthor inner join titles
ON titleauthor.title_id = titles.title_id

INNER JOIN exactly matches the common key on either sides of the Join

–Conventional Inner Joins

SELECT au_id,pubdate
from titleauthor, titles
WHERE titleauthor.title_id = titles.title_id

–Inner Joins having more than two tables

SELECT   au_id,UPPER(pub_name) PUBLISHER,pubdate [Published On]
from       titleauthor
INNER    JOIN titles
ON          titleauthor.title_id = titles.title_id
JOIN      publishers
ON          publishers.pub_id = titles.pub_id
ORDER   BY PUBLISHER

INNER JOIN is the default type of Join. Specifying it is not always needed

–Inner Joins having more than two tables refined with WHERE, AND & OR

SELECT au_id AUTHOR,UPPER(pub_name) PUBLISHER,pubdate [Sys Pub Date],
Convert(char(12),pubdate) [PUBLISHED ON]
from titleauthor
JOIN titles
ON titleauthor.title_id = titles.title_id
JOIN publishers
ON publishers.pub_id = titles.pub_id
WHERE pub_name like 'ALGO%'
AND au_id >= '3%'

Multiple Inner Joins further refined by a Where / And Clause Convert function gives the date in a more friendly format.
Try : Convert(char(20),pubdate) or Convert(char(6),pubdate)
‘>=3%’ will give all the results with au_id starting from 3??-??-???? and above in a lexical order

–Left Join having two tables

select a.title_id,a.title ,b.au_ord ,b.au_id
from titles a
left join titleauthor b
ON a.title_id = b.title_id
order by a.title,b.au_ord

Left Join brings all the rows from table appearing first in the JOIN clause

–Right Join having two tables

select a.title_id,a.title ,b.au_ord ,b.au_id
from titleauthor b 
right join titles a
ON a.title_id = b.title_id
order by a.title,b.au_ord

Right Join brings all the rows from table appearing second in the JOIN clause.

–LeftJoin in two tables having a filter

select                    stor_name,CAST(discount AS VARCHAR) + '%' '%'
from                       discounts
LEFT JOIN              stores
ON                          discounts.stor_id = stores.stor_id
WHERE                   discounts.stor_id IS NOT NULL

The results can be filtered using a trailing Where.
Left join brings all the records from discounts & nulls for unrelated stores records CAST does the same jobs as convert but lacks formatting options Casting needed as % is a character.
Here first ‘%’ becomes part of the data & the second becomes header of the column.

Posted August 18, 2015 by Izharuddin Shaikh in SQL

Tagged with

SQL Server Basic – VI   Leave a comment

–Finding the Grouped SUM based on several filtering criteria

SELECT   STOR_ID [Store Id],SUM(QTY) [Total Quantity]
FROM     sales
WHERE   title_id like 'PC%'
AND       Stor_id between 7000 and 8000
OR          ord_date between '1994-01-01' and '1995-01-01'
GROUP BY STOR_ID

SUM acts just like Count, but instead of number of rows, accumulates the content of any numeric field like QTY here.

–Finding the Average based on several filtering criteria

select avg(discount) avg_discount from discounts
go
SELECT   PAYTERMS,AVG(QTY) [Average Quantity]
FROM     sales
where ord_date < '1994-01-01'
GROUP BY PAYTERMS

AVG is also a grouping function and would average out the quantity against the grouping parameter..

–Grouping By an expression containing a column

select distinct(Left(upper(name),1)) TABLE_INITIALS, count(*) OCCURENCES
from SysObjects
where xtype = 'U'
group by Left(upper(name),1)

Assigning priorities in filtering a query with the help of proper bracketting (parenthesis)

SELECT * from Pubs..sales
WHERE title_id like 'PC%'
AND (
Stor_id between 7000 and 8000
OR ord_date between '1994-01-01' and '1995-01-01'
)
-----
SELECT * from Pubs..sales
WHERE (
title_id like 'PC%'
and Stor_id between 7000 and 8000
)
OR ord_date between '1994-01-01' and '1995-01-01'

In the first query the bracketted WHERE clause gives 2 results and the AND clause gives 19 results. (select * from Pubs..sales where Stor_id between 7000 and 8000 OR ord_date between ‘1994-01-01’ and ‘1995-01-01’ gives 19 results). There intersection gives only 1 result as common.

–In the second query the bracketted WHERE clause gives 1 result
–and the OR clause gives 8 results.Since this is a cumulative OR clause,
–we would get 9 rows in total.

Posted August 18, 2015 by Izharuddin Shaikh in SQL

Tagged with

SQL Server Basic – V   Leave a comment

–Selecting columns based on a Where clause which is further constrained by an AND clause
SELECT * from Northwind..Customers
WHERE Phone like ‘(5)%’
AND RIGHT(Postalcode,2) = ’33’

–Selecting columns based on a Where clause which is further constrained by an AND clause
SELECT * from Northwind..Customers
WHERE Phone like ‘(5)%’
AND RIGHT(Postalcode,2) = ’33’
OR  RIGHT(Postalcode,2) = ’23’

–Selecting columns based on a Where clause which is further constrained by a BETWEEN clause
SELECT Au_LName, YTD_SALES
from Pubs..titleView
where ytd_sales between 2000 and 4000
order by 2

–Finding the Grouped Count based on several filtering criteria
Use Pubs
SELECT STOR_ID, COUNT(*) count
FROM sales
WHERE title_id like ‘PC%’
AND Stor_id between 7000 and 8000
OR ord_date between ‘1994-01-01’ and ‘1995-01-01′
GROUP BY STOR_ID

–GetDate( ) processing with integral days processing
select getdate(), getdate()-1, getdate() + 365

–GetDate( ) processing  with fractional days increment
select getdate() + 1.5
go
select getdate() + 1.45
go

–DateAdd( ) function processing
select hire_date,dateadd(yy,2,hire_date)
from pubs..employee

select hire_date,dateadd(yyyy,2,hire_date)
from pubs..employee

–DateAdd( ) function processing for year increments
select ’15-Aug-1930′ birth_day,dateadd(yy,60,’15-Aug-1930′) retirement_day

–DateAdd( ) function processing for various input date formats
select dateadd(yy,60,’1930-12-13′),
dateadd(yy,60,’1930.12-13′) ,
dateadd(yy,60,’Dec,13 1930′)

–DateAdd( ) function processing for days & weeks increments
select dateadd(dd,-365,hire_date)
from pubs..employee
go

select dateadd(ww,1000,hire_date)
from pubs..employee
go

–DateAdd( ) function processing for months, minutes and seconds increments
Select DATEADD(mm,-3,getdate( ))

Select DATEADD(ss,86400,getdate( )) as Tommorow_Now

Select RIGHT(DATEADD(mi,150,getdate( )),8) as TestEndTime

–Date Conversion formats- Specifying SQL Output formats
select convert(varchar,getdate(), 1)          –Output in mm/dd/yy

select convert(varchar,getdate(), 2)          — Output in yy.mm.dd

select convert(varchar,getdate(), 3)          — Output in dd/mm/yy

select convert(varchar,getdate(), 4)          — Output in dd.mm.yy

select convert(varchar,getdate(), 5)          — Output in dd-mm-yy

select convert(varchar,getdate(), 6)          — Output in dd Mon yy

select convert(varchar,getdate(), 7)          — Output in Mon dd, yy

select convert(varchar,getdate(), 8)          — Output in hh+:mi:ss

select convert(varchar,getdate(), 9)          — Output in Mon dd yyyy hh:mi:ss:mmmXM

select convert(varchar,getdate(), 10)       — Output in mm-dd-yy

select convert(varchar,getdate(), 11)       — Output in yy/mm/dd

select convert(varchar,getdate(), 12)       — Output in yymmdd

select convert(varchar,getdate(), 13)       — Output in Mon dd yyyy hh+:mi:ss:mmm

select convert(varchar,getdate(), 14)       — Output in hh+:mi:ss:mmm

–Date Conversion formats in extended Year notation – specifying SQL Output formats
select convert(varchar,getdate(), 101)     — Output in mm/dd/ yyyy

select convert(varchar,getdate(),102)      — Output in yyyy.mm.dd

select convert(varchar,getdate(),103)      — Output in dd/mm/yyyy

select convert(varchar,getdate(),104)      — Output in dd.mm.yyyy

select convert(varchar,getdate(),105)      — Output in dd-mm-yyyy

select convert(varchar,getdate(),106)      — Output in dd Mon yyyy

select convert(varchar,getdate(),107)      — Output in Mon dd, yyyy

select convert(varchar,getdate(),108)      — Output in hh+:mi:ss

select convert(varchar,getdate(),109)      — Output in Mon dd yyyy hh:mi:ss.mmmXM

select convert(varchar,getdate(),110)      — Output in mm-dd-yyyy

select convert(varchar,getdate(),111)      — Output in yyyy/mm/dd

select convert(varchar,getdate(),112)      — Output in yyyymmdd

select convert(varchar,getdate(),113)      — Output in Mon dd yyyy hh+:mi:ss:mmm

select convert(varchar,getdate(),114)      — Output in hh+:mi:ss:mmm

–Date Conversion formats – specifying Input formats for the varchar dates
select convert(datetime,’12/11/1978′,101)              –Input in mm/dd/ yyyy

select convert(datetime,’1978.12.11′,102)                –Input in yyyy.mm.dd

select convert(datetime,’11/12/1978′,103)              –Input in dd/mm/yyyy

select convert(datetime,’11.12.1978′,104)                –Input in dd.mm.yyyy

select convert(datetime,’11-12-1978′,105)               –Input in dd-mm-yyyy

select convert(datetime,’11 Dec 1978′,106)             –Input in dd-mm-yyyy

select convert(datetime,’Dec 11,1978′,107)             –Input in Mon dd, yyyy

select convert(datetime,’12-11-1978′,110)               –Input in mm-dd-yyyy

select convert(datetime,’1978/12/11′,111)              –Input in yyyy/mm/dd

select convert(datetime,’19781211′,112)  –Input in yyyymmdd

–Date Conversion formats- Input Date format approximation for centuries.
select convert(datetime,’11 Dec 78′,6)      –Input in dd Mon yy

select convert(datetime,’11 Dec 03′,6)      –Input in dd Mon yy

–Date Conversion formats- Input Date format , longest forms and for timestamps .

select convert(datetime,’Dec 11 1978 22:12:12.121′,113)    –Input in Mon dd yyyy hh+:mi:ss:mmm

select convert(datetime,’Dec 11 1978 10:12:12.121PM’,109) –Input in Mon dd yyyy hh:mi:ss.mmmXM

select convert(datetime,’22:12:12.121′,114)             –Input in hh+:mi:ss:mmm

select convert(datetime,’22:12:22′,108)     –Input in hh+:mi:ss

–Date Conversion formats- Input Date format , longest forms and for timestamps .
select * from pubs..employee
where convert(datetime,hire_date,6) > ’11 Dec 93′

select * from pubs..employee where hire_date > ’11 Dec 93′

–Date Conversion formats- Input Date format , longest forms and for timestamps .
select ‘VERIFIED’
where convert(datetime,’11 Dec 93′,6) = convert(datetime,’19931211′,112)

–Date Conversion formats- the ODBC format .
select convert(varchar,hire_date, 121) from pubs..employee where hire_date > ’11 Dec 93′

select substring(convert(varchar,getdate(), 120),12,24)

–Small DateTime format
select getdate(), convert(smalldatetime,getdate( ))

–Days elasped since ‘1.1.1900’
select getdate(),convert(decimal,getdate())

select convert(int,getdate())-convert(int,hire_date) DaysInJob from pubs..employee

–Milliseconds elasped since ‘1.1.1900’

select replace(convert(varchar,getdate(), 114),’:’,”) — stripped time from hh+:mi:ss:mmm

select convert(varchar,getdate(), 112)+replace(convert(varchar,getdate(), 114),’:’,”)

–Unique string generation based on time elasped in milliseconds(?) from absolute zero.
select convert(timestamp,getdate())
go

–Finding the difference in two dates
select datediff(dd,hire_date,getdate()) EmploymentINDays from pubs..employee

select datediff(mm,hire_date,getdate()) EmploymentINMons from pubs..employee

select datediff(yy,hire_date,getdate()) EmploymntINYers from pubs..employee

select datediff(ww,hire_date,getdate()) EmploymntINWeeks from pubs..employee

select datediff(mi,hire_date,getdate()) EmploymntINMinutes from pubs..employee

–Finding Anniversaries in this year
select dateadd(yy,datediff(yy,hire_date,getdate()),hire_date) JOBANNIVERSARY
from pubs..employee
GO
select convert(varchar(6),
dateadd(yy,datediff(yy,hire_date,getdate()),hire_date)) JOBANNIVERSARY
from pubs..employee
GO

–Finding Job Lengths or duration from a fixed past date
select *,datediff(yy,hire_date,getdate()) AS JobYears
from pubs..employee
where datediff(yy,hire_date,getdate()) > 14

–Finding the Year, Month & Date parts from a date expression
select DAY(hire_date) from employee
select MONTH(hire_date) from employee
select YEAR(hire_date) from employee

–Date constituent functions properties
SELECT MONTH(0), DAY(0), YEAR(0)

–DatePart functions
select DATEPART(dw,hire_date) from employee — Day of week when Joining

select DATEPART(dy,hire_date) from employee — Nth Day of Year on Joining

select DATEPART(qq,hire_date) from employee — Quarter of Year at Joining

select DATEPART(mm,hire_date) from employee — Month of Year at Joining

select DATEPART(dd,hire_date) from employee — Day of Month in the year of Joining

Posted August 18, 2015 by Izharuddin Shaikh in SQL

Tagged with

SQL Server Basic – IV   Leave a comment

–Finding the number of rows to be fetched
Select COUNT(*) AS TOTAL_ROWS from Authors

–Finding the number of rows to be fetched, quantified on an attribute
Select state,COUNT(*) AS TOTAL_ROWS
from Authors
group by state

–Finding the number of rows to be fetched, quantified on an attribute,
–substituting any null attribute by some alias or field
Select ISNULL(state,’XX’) STATE,COUNT(*) NOFAUTHORS
from Authors
group by state

–Finding the number of rows to be fetched, quantified by more than one attribute
Select ISNULL(state,’XX’) STATE,Zip,COUNT(*) NOFAUTHORS
from Authors group by state,Zip

–Finding the number of rows, quantified on an attribute satisfying a particular criteria
select state, count(*)
from Authors
group by state
having state is not null

–Finding the number of rows, quantified on an attribute satisfying a particular criteria,
–ordered by the grouping (or a subset of grouping) fields
Select ISNULL(state,’XX’) STATE,Zip,COUNT(*) NOFAUTHORS from Authors
group by state,Zip
order by state,Zip ASC
— order by Zip DESC

–Finding the rows which appear more than once based on a selection criteria
select state, count(*)
from Authors
group by state
having count(*) > 1

–Finding the distinctive counts of an attribute in a table
select COUNT(*) NOF_ROWS,COUNT(state) NOF_VALIDSTATES,
COUNT(distinct state) NOF_DISTINCTSTATES
from Authors

Select COUNT(distinct state) states,  COUNT(distinct zip) zips from Authors

–Finding the resultset based on one restrictive condition
select * from Pubs..Authors
where state = ‘CA’

–Finding the resultset based on few restrictive conditions
select * from Pubs..Authors
where state = ‘CA’
or state = ‘UT’

–Finding the resultset based on few restrictive conditions
select * from Pubs..Authors where state in  ( ‘CA’ ,  ‘UT’  )
order by state desc

–Finding the resultset based bearing some restrictive textual pattern
select * from Northwind..Customers
where CompanyName like ‘B%’

–Finding the resultset based bearing some restrictive textual pattern
select * from Northwind..Customers
where Phone like ‘(5)%’

select * from Northwind..Customers
where substring(PostalCode,1,3) = ‘050’

select * from Northwind..Customers
where Right(Left(Phone,4),3) = ‘208’

Posted August 18, 2015 by Izharuddin Shaikh in SQL

Tagged with

SQL Server Basic – III   Leave a comment

–Embedding incremental spacing between characters in a literal
Select Replace (‘abababa’,’b’,SPACE(0)) away

Select Replace (‘abababa’,’b’,SPACE(1)) away

Select Replace (‘abababa’,’b’,SPACE(2)) away

Select Replace (‘abababa’,’b’,SPACE(3)) away

Select Replace (‘abababa’,’b’,SPACE(4)) away

–Replacing spaces with more spaces
Select Replace (‘The Code of Nimrods  2’,SPACE(1),SPACE(10))
Select Replace (‘The Code of Nimrods  2’,CHAR(32),SPACE(10))

–Replacing spaces with a pattern
Select Replace (‘The wind is getting chilly’,SPACE(1),CHAR(45)) Hyphoon ,

Replace (‘The wind is getting chilly’,SPACE(1),CHAR(47)) Slasher ,

Replace (‘The wind is getting chilly’,SPACE(1),CHAR(95)) LowerDraw ,

Replace (‘The wind is getting chilly’,SPACE(1),CHAR(124)) DigPipe,

Replace (‘The wind is getting chilly’,SPACE(1),’ ‘) Html_Space

–Replacing spaces with a newline
SELECT Replace (‘The wind is getting chilly’,SPACE(1),CHAR(13)) Chinese

–A report like formatting with single SQL
SELECT au_fname + CHAR(32) + Au_lname + CHAR(32) + Phone +
CHAR(13) + Address + CHAR(13) + City + CHAR(32) + state +
CHAR(32) + Zip + replicate(CHAR(13),3) from Authors

–Drawing decorative lines
select Replicate (‘~o~’+SPACE(4),50)

–Stripping of Leading Spaces
select LTRIM(‘     Five spaces are at the beginning of this string.’)

–Stripping of Leading Spaces
select ‘<‘ +  ltrim(‘    middle    ‘) + ‘>’

–Drawing decorative lines
select replicate(space(9)+’———‘,10)
select LTRIM (replicate(space(9)+’———‘,10))

–Stripping of Trailing Spaces
select RTRIM(‘Five spaces are at the end of this string.     ‘)

–Stripping of Trailing Spaces
select ‘<‘+LTRIM(RTrim(‘    middle    ‘)) +’>’

–A mix of Space trimming
select ‘<‘+Type+’>’ WITH_TRAILING_SPACES,'<‘+LTRIM(RTrim(Type)) +
‘>’ LACKING_SPACES from Titles

Posted August 18, 2015 by Izharuddin Shaikh in SQL

Tagged with

Regular Expression – I   Leave a comment

Regular expression are often used to make sure that a string matches a certain pattern. For example, that a string looks like a zip code, or phone number, or e-mail address.

The simplest regular expression is just a sub-string. For example, the regular expression ther matches the string hello there, because the string contains the regular expression.

If you’re familiar with JavaScript regular expression, then you’ll you already know most of this. .NET regular expression are just a super set of JavaScript regular expression.

Start and End of Line

You can easily match strings that start or end with certain characters. The ^ character matched the start of the string. For example:

^hello Matches hello there, hello Shaikh, hellotopical

To match the end
of the string, use the $ character. For example:

ere$
Matches Where, and There

The ^
and $ characters are know as “Atomic Zero Width Assertions”,
in case you were wondering.

Character Classes

Character classes allow you to specify sets of characters or ranges. For example:

[aeiou]
Matches Hey, and Hi, but not Zzz

In other words,
the string must contain at least one of the characters in the character class.
You can also exclude characters. For example:

[^aeiou]
Matches Zzz, but not Hey or Hi.

When the ^
character is the first character in the character class, it means “anything but the following characters”.

Putting this together, we could create a pattern that matches strings that start with a vowel:

[aeiou] Or, strings that don’t start with a vowel: [^aeiou]

With character classes, you can also specify ranges. For example:

[0-9]
Matches 0, 5, 8, or any number between 0 and 9.

[0-9][0-9]
Matches any two digit number (04, 13, 87, etc.), but there’s a better way to do this.

Posted August 16, 2015 by Izharuddin Shaikh in General

Tagged with