All posts by Jon Culp

Microsoft recently announced at the 2017 SQL PASS event that they would be previewing a new tool for sql server that works across multiple operating systems.

“SQL Operations Studio (preview) is a free, light-weight tool, that runs on Windows, macOS, and Linux, for managing SQL Server, Azure SQL Database, and Azure SQL Data Warehouse; wherever they’re running.”

It’s now available for preview.

Read and Download:
https://docs.microsoft.com/en-us/sql/sql-operations-studio/what-is

More info (video) at TechNet:
https://blogs.technet.microsoft.com/dataplatforminsider/2017/11/15/announcing-sql-operations-studio-for-preview/

Links & Lists

The Mother of All Modern Computing Boards

2017-10-22 Jon Culp Leave a comment

I stumbled upon Fran Blanche’s YouTube channel today and can’t get enough, except that I have to cut my self off now and get back to some actual work. She owns a guitar effects pedal company (http://www.frantone.com), tests and builds electronics, reviews old technology, uses 3d printers, she’s an artist, musician, and the list goes on. What a neat human!

“In my opinion it is the most important circuit board in history – and I examine in detail this historic relic from my own personal collection. The LVDC is a strangely obscure footnote in the history of computing, yet it represents some of the greatest leaps that computers ever took. I have spent a good portion of my life designing printed circuit boards, so I have a perspective on this magnificent artifact that I will do my best to convey in this video. But there is so much about this board and its history that I simply do not know – and I hope that some of you who watch this can add insight into the mysteries of the Saturn V Launch Vehicle Digital Computer.” – Fran Blanche

Help Support Fran’s YouTube Channel on Patreon: http://www.patreon.com/frantone

Subscribe to Fran’s YouTube Channel: https://www.youtube.com/franblanche

Databases, MS SQL Server 2008 R2, MS SQL Server 2016, Tech Notes

Remove Numbers from Text SVF, Nested vs CTE

2017-10-11 Jon Culp Leave a comment

I need to remove numbers from a string while processing some data as part of an ETL process in a data warehouse. I have to do this for many columns and potentially many tables so I decided to put the logic into a Scalar-Valued Function (SVF), understanding there are performance detriments when using SVFs. Generally this will be used on relatively small data sets for cleaning “codes” that will be insert into a slowly changing dimension. I was curious about how to do this. My instinct was to do it using nested replace functions. I did a quick google search to see what options the collective would recommend. I came across a post on Stack Overflow that mentioned the nested replace functions, and then someone also mentioned using a recursive Common Table Expression (CTE), which I thought was a creative suggestion.

https://stackoverflow.com/questions/13240298/remove-numbers-from-string-sql-server/

I have a soft spot in my heart for recursive CTEs so I thought I’d try both options and see which solution performs better. Let’s start with building the functions:

--create Nested function
create function udf_RemoveNumbers_Nested
	(
		-- Add the parameters for the function here
		@String varchar(200)
	)
returns varchar(200)
as
begin
	-- Declare the return variable here
	declare @Result varchar(200);

	-- Add the T-SQL statements to compute the return value here
	select
		@Result = 
		replace(
		 replace(
		  replace(
		   replace(
		    replace(
			 replace(
			  replace(
			   replace(
			    replace(
				 replace( @String, '0', '' )
				 , '1', '' )
				, '2', '' )
			   , '3', '' )
			  , '4', '' )
			 , '5', '' )
			, '6', '' )
		   , '7', '' )
		  , '8', '' )
		 , '9', '' );

	-- Return the result of the function
	return @Result;

end;
go

--creat CTE function
alter function udf_RemoveNumbers_CTE
	(
		-- Add the parameters for the function here
		@String varchar(200)
	)
returns varchar(200)
as
begin
	-- Declare the return variable here
	declare @Result varchar(200);

	-- Add the T-SQL statements to compute the return value here
	;
	with cte
	as ( select
				@String as string
			  , 0 as n
		 union all
		 select
				convert( varchar(200), replace( string, n, '' ))
			  , n + 1
		 from	cte
		 where	n <= 9
	   )
	select
		@Result = (
					  select top 1	string from cte order by n desc
				  );

	-- Return the result of the function
	return @Result;

end;
go

--create Nested function

create function udf_RemoveNumbers_Nested

(

-- Add the parameters for the function here

@String varchar(200)

)

returns varchar(200)

begin

-- Declare the return variable here

declare @Result varchar(200);

-- Add the T-SQL statements to compute the return value here

select

@Result =

replace(

replace( @String, '0', '' )

, '1', '' )

, '2', '' )

, '3', '' )

, '4', '' )

, '5', '' )

, '6', '' )

, '7', '' )

, '8', '' )

, '9', '' );

-- Return the result of the function

return @Result;

end;

--creat CTE function

alter function udf_RemoveNumbers_CTE

(

-- Add the parameters for the function here

@String varchar(200)

)

returns varchar(200)

begin

-- Declare the return variable here

declare @Result varchar(200);

-- Add the T-SQL statements to compute the return value here

;

with cte

as ( select

@String as string

, 0 as n

union all

select

convert( varchar(200), replace( string, n, '' ))

, n + 1

from cte

where n <= 9

)

select

@Result = (

select top 1 string from cte order by n desc

);

-- Return the result of the function

return @Result;

end;

Now that we have some functions we need some test data so let’s create a simple table and populate it with simi-random data by leveraging the newid() function.

Continue reading Remove Numbers from Text SVF, Nested vs CTE →

Arts, Photography

Photography Highlights

2017-10-09 Jon Culp Leave a comment

Arts, Photography

Phil and Rocky

2017-10-05 Jon Culp Leave a comment

Arts, Poetry

Two

2017-10-03 Jon Culp 1 Comment

I don’t sleep, but I suddenly wake
My head is a race track
Your toy car follows each age line in my face
I smile
New lines are formed

Today you are two

Height, weight, and age can be measured
You can not measure my patience
But you know how to test it
Bip the Clown is no mute
A handsome Blok nonetheless
You can not measure my happiness
But you know how to grant it

Preparing for work
Drab as a fool, aloof as a bard
I daydream we are an adagio pair
And you are Atlantis soaring
Toothpaste drips onto my shirt

Stomping
Here comes the son
Winked an eye as you pointed your finger…
“I pooped Dada!”

Soon there will be two

Continue reading Two →

Links & Lists

Simon Sinek: The Video That Will Change Your Life

2017-10-02 Jon Culp Leave a comment

My boss had our department watch this today to give us some perspective on personal and workplace relationships. Specifically, to give us some perspective when trying to open the minds of our co-workers to business intelligence. I tend to hate “motivational” or “self help” types of videos but this one has a science twist that really spoke to me.

About this presentation:
In this in-depth talk, ethnographer and leadership expert Simon Sinek reveals the hidden dynamics that inspire leadership and trust. In biological terms, leaders get the first pick of food and other spoils, but at a cost. When danger is present, the group expects the leader to mitigate all threats even at the expense of their personal well-being. Understanding this deep-seated expectation is the key difference between someone who is just an “authority” versus a true “leader.”

Inspiration/Future Projects, Projects

Creating a Doom-style 3D engine in C

2017-04-07 Jon Culp Leave a comment

In this tool-assisted educational video Bisqwit walks through the creation of a Duke Nukem 3D style software-rendering pseudo-3D engine from scratch. Topics such as vector rotation and portal rendering are at the core.

Links & Lists

Introducing Google A.I. Experiments

2017-03-22 Jon Culp Leave a comment

Explore machine learning by playing with pictures, language, music, code, and more.

Check it out: https://aiexperiments.withgoogle.com/

Here is an inspiring TED talk on AI that has more of a positive spin than is often given to the future prospects of artificial intelligence and how it will fit in with society. One of the main points is “Making people want stuff we make” vs “Making stuff people want” by using AI to give us the insight we would not otherwise realize.

Jon Culp [.com]

All posts by Jon Culp

Strange Parts: Bringing BACK The iPhone Headphone Jack – in China

Microsoft Released SQL Operations Studio (preview)

The Mother of All Modern Computing Boards

Remove Numbers from Text SVF, Nested vs CTE

Photography Highlights

Phil and Rocky

Two

Simon Sinek: The Video That Will Change Your Life

Creating a Doom-style 3D engine in C

Introducing Google A.I. Experiments

An Information Archive for a Frivolous Human