Good day and welcome to SQL Something!
I'm particularly pleased to write this one as it's the first non-MiniPost I've written in a while. Huzzah!
Today we are going to look at how to get the time difference between the insertion of two rows, given a datetime column.
"Musings from a DBA in training." - Geon Bell
Monday 18 November 2013
Friday 15 November 2013
Mini Post: Removing all Whitespace/NewLine Characters From a String
Hey and hello! Welcome back or welcome new!
Today really quickly we'll look at something that is very helpful in my day-to-day stuff, that is, stripping all spaces and any new line characters from a string.
You can use the below:
REPLACE(REPLACE(REPLACE(REPLACE(YourString, CHAR(9), ''), CHAR(10), ''), CHAR(13), ''), ' ', '');
This strips Tab spaces ( represented by CHAR(9) ), Line Feed spaces ( represented by CHAR(10) ), Carriage Returns ( represented by CHAR(13) ) and plain old spaces (represented by ' ').
Please see here for CHAR types.
DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
Today really quickly we'll look at something that is very helpful in my day-to-day stuff, that is, stripping all spaces and any new line characters from a string.
You can use the below:
REPLACE(REPLACE(REPLACE(REPLACE(YourString, CHAR(9), ''), CHAR(10), ''), CHAR(13), ''), ' ', '');
This strips Tab spaces ( represented by CHAR(9) ), Line Feed spaces ( represented by CHAR(10) ), Carriage Returns ( represented by CHAR(13) ) and plain old spaces (represented by ' ').
Please see here for CHAR types.
DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
Monday 4 November 2013
Mini Post: Getting distinct random values using 'TOP' and 'NEWID()'
Heya everyone out there, Mini Post time!
...Too many of these don't you think? I really have to buckle down and write a longer post sometime.
Anyway let's focus on something I had to use 5 minutes ago (I have used it before, but 5 minutes ago was the most recent so I figured I'd jot this down quickly while its in my head).
Sometimes we wish to get back random rows from our table. For this we can use 'NEWID()' as follows:
SELECT top 10 Col1
FROM Table1
-- WHERE SomePredicate
ORDER BY NEWID();
The above will pull 10 random Col1 values, regardless if the same value can appear multiple times in Col1.
Now sometimes we wish to pull not only random values, but random distinct values.
It'd be nice if we could do something like:
--Incorrect
SELECT top DISTINCT 10 Col1
FROM Table1
-- WHERE SomePredicate
ORDER BY NEWID();
But alas, we'll get an error. So instead please use (the key point is the 'GROUP BY'):
--Correct
SELECT top 10 Col1
FROM Table1
-- WHERE SomePredicate
GROUP BY Col1
ORDER BY NEWID();
DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
...Too many of these don't you think? I really have to buckle down and write a longer post sometime.
Anyway let's focus on something I had to use 5 minutes ago (I have used it before, but 5 minutes ago was the most recent so I figured I'd jot this down quickly while its in my head).
Sometimes we wish to get back random rows from our table. For this we can use 'NEWID()' as follows:
SELECT top 10 Col1
FROM Table1
-- WHERE SomePredicate
ORDER BY NEWID();
The above will pull 10 random Col1 values, regardless if the same value can appear multiple times in Col1.
Now sometimes we wish to pull not only random values, but random distinct values.
It'd be nice if we could do something like:
--Incorrect
SELECT top DISTINCT 10 Col1
FROM Table1
-- WHERE SomePredicate
ORDER BY NEWID();
But alas, we'll get an error. So instead please use (the key point is the 'GROUP BY'):
--Correct
SELECT top 10 Col1
FROM Table1
-- WHERE SomePredicate
GROUP BY Col1
ORDER BY NEWID();
DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
Subscribe to:
Posts (Atom)