Archive for August 2015
//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
–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.
–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.
–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
–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’
–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
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.