How to order a result set in SQL based on the numeric part of the string value

There are occasions where you would like to search in a varchar column and order the results based on the numeric part of the field.

Imagine the following table:

ABC
ABC1
ABC2
ABC3 and so on...

ABC11
ABC12
ABC13 and so on..

ABC20
ABC21
ABC22 and so on..

So basically what I have is any string value (not always ABC, any string value) that can either be followed by the number or it may just be a string without the number.

When you run

select * from table order by my column asc

you get following results:

ABC
ABC1
ABC11
ABC12
ABC13
ABC2
ABC20
ABC21
ABC22
ABC3
ABC31
ABC32

In this scenario, you would need the results sorted by the numeric value and not alphabetically.
Eg:

ABC
ABC1
ABC2
ABC3
ABC11
ABC12
ABC13
ABC20
ABC21
ABC22
ABC31
ABC32

How can this be accomplished?

Continue reading “How to order a result set in SQL based on the numeric part of the string value”

Allow for the possibility of a part of the URL being removed

Consider what happens when a user removes part of your URL Some users might navigate your site in odd ways, and you should anticipate this. For example, instead of using the breadcrumb links on the page, a user might drop of a part of the URL in the hopes
of finding more general content.

He or she might be visiting http://
http://www.brandonsbaseballcards.com/news/2016/upcoming-baseballcard-shows.htm,

but then enter http://www.brandonsbaseballcards.com/news/2016/ into the browser’s address bar, believing that this will show all news from 2016. Is your site prepared to show content in this situation or will it give the user a 404 (“page not found” error)? What about moving up a directory level to http://www.brandonsbaseballcards.com/news/?

A site map (lower-case) is a simple page on your site that displays the structure of your website, and usually consists of a hierarchical listing of the pages on your site.

Visitors may visit this page if they are having problems fiding pages on your site.

While search engines will also visit this page, getting good crawl coverage of the pages on your site, it’s mainly aimed at human visitors.

An XML Sitemap (upper-case) file, which you can submit through Google’s Webmaster Tools, makes it easier for Google to discover the pages on your site. Using a Sitemap fie is also one way (though not guaranteed) to tell Google which version of a URL you’d prefer as the canonical one (e.g. http://brandonsbaseballcards.com/ or http://www.brandonsbaseballcards.com/; more on what’s a preferred domain). Google helped create the open source Sitemap Generator Script to help you create a Sitemap file for your site. To learn more about Sitemaps, the Webmaster Help Center provides a useful guide to Sitemap fies

Capture

Make your site easier to navigate

Navigation is very important for search engines

The navigation of a website is important in helping visitors quickly fid the content they want. It can also help search engines understand what content the webmaster thinks is important.
Although Google’s search results are provided at a page level, Google also likes to have a sense of what role a page plays in the bigger picture of the site.

Plan out your navigation based on your homepage

All sites have a home or “root” page, which is usually the most frequented page on the site and the starting place of navigation for many visitors. Unless your site has only a handful of pages, you should think about how visitors will go from a general page (your root page) to a page containing more specific content. Do you have enough pages around a specific topic area that it would make sense to create a page describing these related pages (e.g. root page -> related topic listing -> specific topic)?

Capture
Site structure

Do you have hundreds of different products that need to be classified under multiple category and subcategory pages?

404 (“page not found” error)
An HTTP status code. It means that the server could not find the web
page requested by the browser.

Ensure more convenience for users by using ‘breadcrumb lists’

A breadcrumb is a row of internal links at the top or bottom of the page that allows visitors to quickly navigate back to a previous section or the root page (1). Many breadcrumbs have the most general page (usually the root page) as the fist, left-most link and list the more specific sections out to the right.

Capture
Breadcrumb links appearing on a deeper article page on your site

XML Sitemap
A list of the pages on a particular website. By creating and sending this list, you are
able to notify Google of all pages on a website, including any URLs that may have been
undetected by Google’s regular crawling process.

 

 

How to re-order rows in a table with incorrect ordering in SQL

Capture

Imagine you have this scenario – the order of a table which relies on its order has been muddled up by some bad code and you wish to sort it out with the least effort possible.

You can write a quick SQL script which will reset the order to the right set (seen on the right).

To find out what your order should be, include the ROW_NUMBER() command as below:

select *
,ROW_NUMBER() OVER(ORDER BY Orders ASC) AS Row
from DM_ReportColumns where [conditions]

Then you can run an update based on your primary keys (in this scenario, I have ReportID, Username and ColumnID as keys.

UPDATE DM_ReportColumns SET Orders = x.Row 
from (
select *
,ROW_NUMBER() OVER(ORDER BY Orders ASC) AS Row
 from DM_ReportColumns where ReportID  = 'A01' and Username = 'user1'
) as x
WHERE x.ReportID = DM_ReportColumns.ReportID 
AND x.Username = DM_ReportColumns.Username 
AND x.ColumnID = DM_ReportColumns.ColumnID

After

Capture2

A lottery industry insider installs undetectable software giving him advance knowledge of winning numbers, then enlists accomplices to play those numbers and collect the jackpots.

Eddie Tipton, the former security director of the Multi-State Lottery Association, reportedly installed software code to be able to know winning numbers.

playhugelottos.com 390-lottery-0415Eddie was convicted of rigging the $16.5m jackpot in Iowa in 2015. At the moment, he is awaiting trial in four other states. The lottery security director was charged after being identified on the surveillance footage of a person buying the winning ticket for a $16.5m jackpot in 2010. Tipton, who had unparalleled access to lottery software, was identified on the footage by his colleagues.

Prosecutors say Tipton installed software known as a root kit that enabled him to manipulate numbers without a trace. What tripped him up, investigators say, was his decision to buy the winning ticket himself at a service station near the headquarters of the association, whose workers are prohibited from trying their luck.

Continue reading “A lottery industry insider installs undetectable software giving him advance knowledge of winning numbers, then enlists accomplices to play those numbers and collect the jackpots.”

Spam of the Day – Receipt for Your Payment to Uk-AdCommerce-EOM@ebay.com

Hello,

You authorised a payment of 37.81 GPB to eBay International UK (UK-ebay-inc-admin@ebay.co.uk)

Your funds will be transferred when the merchant processes your payment. Any money in your PayPal account balance will be used first. If you have a zero balance or insufficient funds in your account, your backup funding source will be charged for the full or remaining payment. Please note that your bank or card provider may charge a dishonour fee if you have insufficient funds to make the payment.

Thanks for using PayPal. To view the full transaction details, log in to your PayPal account.

Email1

Email2

Let me tell you how to spot the phishing details: Continue reading “Spam of the Day – Receipt for Your Payment to Uk-AdCommerce-EOM@ebay.com”

Five Easy Steps to Staying Motivated

Motivating yourself to excel at your job or to be an example to your employees, should not be something you do only when the spirit moves you.  It’s an ongoing process that should include every facet of your business life. This means your mental attitude, physical well-being and appearance, work atmosphere, your interaction with others (clients and employees alike), and your off-the-job environment.

Motivational experts get paid big bucks to tell professionals, striving for success, that they must constantly examine these factors.  How do you do that?  Follow the 5 tips that follow, and watch the changes. Continue reading “Five Easy Steps to Staying Motivated”

ARE YOU PADDLING OR FLOATING?

Ask yourself are you paddling or floating your canoe down the river of your business life?  If you’re floating then you’re on the defense, if you’re paddling then you’re on the offense – where do you want to be?

In today’s business climate if you’re not on the offensive then you’re being whipped and buffeted from every side by the obstacles you encounter.  If something isn’t working in your advertising and you’re a floater then you just wait until next week, or next month and see if it improves.  But if you’re paddling and guiding your business, then you’re busy working out how to fix what’s not working. Continue reading “ARE YOU PADDLING OR FLOATING?”