Just another WordPress site
Scott’s Marble Runs knows a thing or two about making really long tracks for his marbles to zoom down. This time, he created an epic, multi-story run that has dozens upon dozens of loops, and relies heavily on gravity to make it work. Nice catch there at the end too, Scott.
If you’ve ever played with one of those drinking bird toys, you know it can be quite fascinating to watch as it dunks its beak in and out of a glass of water. Engineerguy Bill Hammack pops off the bird’s festive blue hat to explain the thermodynamics which make the nearly endless fun happen.
SQL CONCAT_WS Function Example | CONCAT_WS Method In SQL is today’s topic. The SQL CONCAT_WS function is used to join two or more expressions separated by a separator. It takes up to 255 input strings which are further joined by a function. If we want to perform the join operation, CONCAT requires at least 2 strings. If it is provided with only 1 string, it will raise the error.
If any non-character string values are passed into the function, it will be implicitly converted to the strings before concatenating.
If NULL is added to the function, it converts it into the empty string with VARCHAR (1).
See the following syntax.
SELECT CONCAT_WS (separator, expression1, expression2, expression3…)
This is the separator that is added between more than one expression. If the separator is NULL, then the function will return NULL.
These are the expressions that will be concatenated.
See the following example. We are using SQL SELECT Statement.
SELECT CONCAT_WS ('.', 'AppDividend', 'com');
AppDividend.com
Here “.” acts as a separator and is added between AppDividend and com.
See the following code.
SELECT CONCAT_WS (',', 1, 2, NULL, NULL, 3);
1, 2, 3
The above example demonstrated how concat_ws() function deals with NULL values. Here, the NULL values were ignored, and no separator was added between them.
Above were all the common examples to make clear how the Concat function works.
Let’s see the example with proper tables.
Consider table Employee.
| Emp_id | First_name | Last_name | City | State | Salary |
| 101 | Rohit | Raj | Patna | Bihar | 30000 |
| 201 | Shivam | Rana | Jalandhar | Punjab | 20000 |
| 301 | Karan | Kumar | Allahabad | Uttar Pradesh | 40000 |
| 401 | Suraj | Bhakat | Kolkata | West Bengal | 60000 |
| 501 | Akash | Cherukuri | Vizag | Andhra Pradesh | 70000 |
Now, suppose we want the full name of an employee from this table. Then, the following query has to be executed.
Select First_name, Last_name, concat_ws(‘ ’, First_name, Last_Name) AS Full_name from Employee;
| First_name | Last_name | Full_name |
| Rohit | Raj | Rohit Raj |
| Shivam | Rana | Shivam Rana |
| Karan | Kumar | Karan Kumar |
| Suraj | Bhakat | Suraj Bhakat |
| Akash | Cherukuri | Akash Cherukuri |
Here you can see that the full name of the employee is displayed separated by a separator which was space.
The CONCAT_WS() function joins the input strings into the single string.
It separates those concatenated strings with a separator particular in the first argument.
One thing to note that the CONCAT_WS() requires at least two input strings.
It means that if we pass zero or one input string argument, the function will raise the error.
The CONCAT_WS() function treats NULL as the empty string of type VARCHAR().
It also does not add a separator between NULLs.
Therefore, a CONCAT_WS() function can cleanly join the strings that may have blank values.
The following statement uses the CONCAT_WS() function to join the values in the last_name and first_name columns of the sales.customer table.
It separates the last name and first name by the comma (,) and space.
SELECT
first_name,
last_name,
CONCAT_WS(', ', last_name, first_name) full_name
FROM
sales.customers
ORDER BY
first_name,
last_name;
The following statement indicates how the CONCAT_WS() function handles input strings that have NULL values.
SELECT
CONCAT_WS(',', 1, 2, NULL, NULL, 3);
The output is as follows:
result ---------------------------------------- 1,2,3 (1 row affected)
As you can see from the above output, the CONCAT_WS() function ignores NULL and don’t add the separator between the NULL values.
The following example concatenates the customer data to format customer’s addresses. If the customer does not have a phone number, that function ignores it.
SELECT
CONCAT_WS
(
CHAR(13),
CONCAT(first_name, ' ', last_name),
phone,
CONCAT(city, ' ', state),
zip_code,
'---'
) customer_address
FROM
sales.customers
ORDER BY
first_name,
last_name;
This statement uses the comma (,) as a separator and concatenates values in the first_name, last_name, and email column to generate the CSV file.
See the following query.
SELECT
CONCAT_WS(',', first_name, last_name, email)
FROM
sales.customers
ORDER BY
first_name,
last_name;
So, In this tutorial, you have learned how to use the SQL CONCAT_WS() function to concatenate strings with a separator.
Finally, SQL CONCAT_WS Function Example is over.
SQL DIFFERENCE Function Example
SQL LIKE Operator Tutorial With Example
SQL Operators Tutorial With Example
The post SQL CONCAT_WS Function Example | CONCAT_WS Method In SQL appeared first on AppDividend.
via Planet MySQL
SQL CONCAT_WS Function Example | CONCAT_WS Method In SQL
Dear MySQL users,
The MySQL developer tools team announces 8.0.18 as our General Availability
(GA) for MySQL Workbench 8.0.
For the full list of changes in this revision, visit
http://dev.mysql.com/doc/relnotes/workbench/en/changes-8-0.html
For discussion, join the MySQL Workbench Forums:
http://forums.mysql.com/index.php?152
The release is now available in source and binary form for a number of
platforms from our download pages at:
http://dev.mysql.com/downloads/tools/workbench/
Enjoy!
Courtesy ATF and CNN
First, credit where it’s due. CNN’s Scott Glover has managed to turn out an excellent article about a fairly arcane aspect of guns and firearms law while getting the details right. That’s a notable feat for legacy media these days. Read the whole thing here.
With that out of the way, the criminal prosecution — aborted though it was — that Glover has written about is worthy of note and could make the ATF’s job of regulating AR-15 sales going forward extremely difficult. CNN’s article is titled, He sold illegal AR-15s. Feds agreed to let him go free to avoid hurting gun control efforts.
Here are the particulars. A Southern California man named Joseph Roh produced 80% AR-15 lowers and complete rifles, some of which he allegedly sold without a manufacturer’s license, and some allegedly to prohibited persons. At least a few of the guns he sold were used in crimes including an 80% lower that was used as the basis for a rifle build used in a 2013 spree shooting in Santa Monica.
The ATF had been watching Roh for years and mounted a sting operation against him in 2014. They sent undercover agents into his south LA machine shop where he was holding what were basically “build parties” where customers finished lowers and assembled completed rifles.
Roh was eventually arrested and charged with running an unlicensed firearms manufacturing operation. But none of that is the interesting part of the story.
The aspect that’s worthy of your attention — and is no doubt giving the ATF nightmares — is the argument that Roh’s attorney made in successfully defending his client.
As you probably know, the only part of an AR-15 that’s legally considered a firearm is the lower receiver. That’s the part that’s serialized and requires a background check to purchase (unless you buy an 80% lower and finish it yourself, but that’s another story).
Joseph Roh was smart enough to hire a good attorney, Gregory Nicolaysen. Nicolaysen did his homework and actually read the federal statue that lays out what constitutes — legally speaking — a firearm. When Roh’s case came to trial in 2018 . . .
Nicolaysen argued that the definition of a receiver under the relevant federal code differed in various ways from the AR-15 component Roh was accused of manufacturing.
Under the US Code of Federal Regulations, a firearm frame or receiver is defined as: “That part of a firearm which provides housing for the hammer, bolt or breechblock, and firing mechanism, and which is usually threaded at its forward portion to receive the barrel.” (emphasis added)
The lower receiver in Roh’s case does not have a bolt or breechblock and is not threaded to receive the barrel, Nicolaysen noted.
And neither does any other AR-15 lower receiver. Where most firearms have a monolithic receiver that meets the definition under federal law, an AR has a split receiver, an upper and a lower. Neither component, strictly speaking, meets the definition of a frame or receiver that is explicitly laid out in the law.
In effect, Nicolaysen argued that the ATF’s interpretation of federal law that they’ve been using to deem AR-15 lowers as legal firearms is wrong…and has been since, well, forever.
(Nicolaysen) called the decision to classify it as a firearm nonetheless, the result of “secret, in-house decision-making.”
Nicolaysen accused the ATF of abusing its authority by pursuing Roh based on his alleged violation of a policy “that masquerades as law.”
Roh’s case was heard in a bench trial (at his option) in which only the judge hears the evidence and renders a verdict. US District Court Judge James V. Selna deliberated for a year and then wrote a tentative order in April.
Selna agreed with Roh’s argument that the ATF’s definition of an AR-15 lower as a firearm is faulty.
That, no doubt, set off alarm bells from LA to DC. If the ruling were allowed to stand, that would set a very inconvenient precedent, one that would make AR-15 lowers like any other part of an AR platform rifle…just another gun part that could be made and sold through the mail to just about anyone. No serial number or background check needed.
The ATF couldn’t let that stand, so prosecutors reached a plea deal with Roh.
Selna did find that Roh was guilty of selling completed firearms without a license, subjecting him to a possible prison sentence.
Following Selna’s tentative order, the prosecution and defense agreed to a deal in which Roh would plead guilty to the charge against him, but would be allowed to withdraw that plea if he stayed out of trouble for a year. Prosecutors would then dismiss the case. If Roh abides by the deal, he will have no criminal conviction and serve no time behind bars.
And there would be no legal precedent.
Sources familiar with the agreement said prosecutors wanted to strike a deal in order to prevent Selna’s order from becoming permanent, drawing publicity, and creating case law that could hamper ATF enforcement efforts.
They basically let Roh walk in order to preserve the current fiction under which the ATF regulates AR-15 sales.
As for “drawing publicity,” CNN has done a good job of that with their story. And, as Glover points out, Roh’s case wasn’t the first time a similar argument had been successfully used.
Federal law enforcement officials — and members of Congress — have been on notice about a potential problem with the language in federal gun law as applied to AR-15s since at least 2016.
In July of that year, prosecutors in Northern California abandoned a case against a convicted felon named Alejandro Jimenez after a judge found that the AR-15 lower receiver he was accused of purchasing in an ATF undercover sting did not meet the definition of a receiver under the law.
The ruling and subsequent dismissal drew little notice but prompted a letter to Congress from then-US Attorney General Loretta Lynch. She advised lawmakers that the judge’s decision was not suitable for appeal and that if ATF officials believed the definition should be changed, they should pursue regulatory or administrative action.
You can read the court’s findings in the Jimenez case here (PDF) sent to us by an attorney friend of TTAG. Note in particular the areas highlighted in yellow.
So the government has known that the ATF is using a faulty interpretation of federal law to regulate the sale of AR-15 lowers for decades now. And the deal they cut in the Roh prosecution doesn’t change that in the slightest.
“AR-15s, as we speak today, do not have a receiver by the definition of the existing law and that’s a huge issue,” (Nicolaysen) said. “It shows that the laws are obsolete and they’re out of sync with the realities of today’s firearms market.”
(Adam) Winkler, the UCLA law professor, offered a similar assessment.
When he was first informed of the judge’s tentative order by a CNN reporter, Winkler said, “I thought the logic was crazy.”
But after reviewing the order and several filings in the case at the request of CNN, he said Selna’s rationale appeared legally sound.
“It does seem like there is problem,” Winkler said.
It certainly does.
The only way to fix this is through new legislation. Congress alone can change federal law to define a frame or receiver in such a way that AR-15 rifles are covered. That’s why Attorney General Lynch wrote the letter she did back in 2016, suggesting a legislative fix. But Congress apparently shrugged that off.
TTAG has reached out to Gregory Nicolaysen for comment on the case, but a call and a text haven’t been returned yet.
Under a legal principle called Chevron deference, federal courts give regulatory agencies like the ATF wide latitude in interpreting and enforcing Congress’s often poorly-written laws. However, when the agency’s interpretation is so clearly at odds with the underlying language of the law, even Ninth Circuit judges can’t overlook the problem.
(Nicolaysen) asked the judge to consider recommending that then-US Attorney General Jeff Sessions conduct a review to determine whether there were any similar cases pending around the country or past convictions “sustained on the basis of ATF policy, rather than law.”
The argument that Roh’s attorney employed to get his client a very good deal will no doubt be used by defendants in future prosecutions. It could also be used by other attorneys to try to reverse previous convictions of those found guilty on similar charges.
via The Truth About Guns
The ATF’s Definition of an AR-15 Lower as a ‘Firearm’ Is In Serious Trouble
If you look back at how computers have been programmed over the years, the languages used have shuffled around quite a bit. From the early days of Fortran, to the rise of BASIC, to the explosion of Java, PHP, and Python, Data Is Beautiful charted the changing popularity of each major language over more than 50 years.
Craftable provides a responsive UI based on Bootstrap 4 and CoreUI
Quickly scaffold an administration CRUD based on your existing database structure
Add to your existing Laravel 6 project or create a new one using Craftable installer
Authentication, Forgot password, Remember me, Activation, My profile & User CRUD
Search & manage all localized static text (even with only one language)
Associate media with Eloquent models using nice drag’n’drop media uploader
Attach permissions and/or roles to Users and authorize the actions
Make your Eloquent translatable and localize all of your content to the defined languages
Craftable comes with straightforward, up-to-date documentation
via Laravel News Links
CRAFTABLE – admin panel & CRUD generator for Laravel 6
Laravel released v6.2 yesterday with a new password confirmation feature that enables you to require a logged-in user to re-enter their password before being allowed access to a route.
This functionality works like the GitHub confirmation screen when you perform sensitive actions. Setting it up is a breeze in Laravel, so let’s take the new feature for a spin so you can see how it works:
First, let’s create a new Laravel application to work with so you can visualize how this new feature works:
laravel new confirm-app cd confirm-app composer require laravel/ui --dev If you remember, the make:auth command was removed in Laravel 6 and moved to the laravel/ui first-party package. Let’s generate the authorization code for our app:
php artisan ui vue --auth yarn install yarn dev Next, let’s configure an SQLite database (feel free to use whatever driver you want):
touch database/database.sqlite We’ve created the file that Laravel will look for by default when using the sqlite driver, but you’ll either need to update the .env file with the correct connection and database path:
DB_CONNECTION=sqlite # ... # Use the default path of the sqlite driver # DB_DATABASE=laravel Next, let’s run migrations and then create a test user:
php artisan migrate We can create a test user with the console via the factory() :
php artisan tinker >>> $user = factory(App\User::class)->create([ ... 'password' => bcrypt('secret'), ... 'email' => 'admin@example.com' ... ]); Let’s say that you wanted users to re-authenticate their password before viewing an administration action like adding an SSH key. We would want the user to re-enter their password within the configured window (the default is 3 hours).
We’ll create a fake /settings/ssh/create route where we’ll require the new password.confirm middleware before the user can create a new key:
php artisan make:controller Settings/SSHController Next, create the controller action create():
namespace App\Http\Controllers\Settings; use App\Http\Controllers\Controller; use Illuminate\Http\Request; class SSHController extends Controller { public function create() { return view('secret'); } } We’ll stub out the secret template, which we put in the root of the views path resources/views/secret.blade.php:
@extends('layouts.app') @section('content') <div class="container"> <div class="row justify-content-center"> <div class="col-md-8"> <h1>Add a New SSH Key</h1> <p>This page is only shown after password confirmation.</p> </div> </div> </div> @endsection At the time of writing, you need to copy the auth/passwords/confirm.blade.php file to your project. You can get the stub here: ui/confirm.stub. Copy this file and add it to your project in the following path:
resources/views/auth/passwords/confirm.blade.php Next, we need to define the route, along with the middleware we’ll need for this at the end of the routes/web.php file:
Route::namespace('Settings') ->middleware(['auth']) ->group(function () { Route::get('/settings/ssh/create', 'SSHController@create')->middleware('password.confirm'); }); Note: typically, you might group all routes requiring authentication auth routes. For this demo, we’re creating one controller route in the Settings namespace using
With that in place, once you log in, you’ll be redirected to /home. From there, navigate to /settings/ssh/create, and then you’re prompted for your password:
If you followed along with this tutorial, enter secret, submit the form, and then you will be taken to the create view. You can refresh this page without being prompted after confirming the password.
Using the new ddd() Helper, add this to your SSHController::create() method to visualize the auth.password_confirmed_at session value that determines the next time you’ll be prompted:
public function create() { ddd(session('auth')); return view('secret'); } 
This value was the timestamp when the password was last confirmed. By default, the middleware will not re-prompt for three hours. You can control how long before the user should confirm the password again with the config('auth.password_timeout') value located in config/auth.php as of Release v6.2.0.
First of all, thanks Dries Vints for this wonderful new feature! Check out Pull Request #5129 for the implementation details. Any new Laravel applications created with Laravel 6.2 include this new middleware!
Filed in: News
No Spam, ever. We’ll never share your email address and you can opt out at any time.
via Laravel News
New Password Confirmation Flow for Logged In Users in Laravel 6.2
The What’s Inside? channel presents one of its more costly videos, as they rip apart open one of the powerful drive motors from a Tesla Model S to see all of the gears, goo, and other goodies inside. This particular rear motor dates back to 2012, and was purchased off of eBay.