Monday, September 26, 2011

Finding the Character Set For a SQL Field

The other day I was working on a project and I needed to know all of the different types of characters contained in a SQL field so that I could make a regular expression pattern that would not fail. Thus, I decided to write a stored procedure that would loop through a range of characters and test if that character is contained in a specified field in any records. From there I can get a list of all of the used characters and then more intelligently write my regular expression pattern.

This could also be handy if you are trying to cast a field and you keep getting errors because of a character that won't cast correctly. An example would be trying to cast a varchar or char field into an int and for some reason or another one of the records has a letter in the field. Your cast will fail and it can be tricky to track down where the issue is. With this stored procedure you can quickly see what the offending character is and then query up all the records containing that character.

Example usage:
exec FieldCharacterSet 'dbo', 'mytable, 'myfield', 1, 0, 255

Feel free to use this if you find is useful, and leave a comment letting me know if it worked well for you.

-- =============================================
-- Author:		Davin Studer
-- Create date: 9/26/2010
-- Description:	This stored procedure will let you
-- know all the characters that are contained within
-- a field of a specified table.  This is handy for things
-- such as creating regular expression patterns and figuring
-- out casting issues.
-- =============================================
create procedure [dbo].[FieldCharacterSet]
	@TableSchema nvarchar(255) = '',-- this is the schema for table to look at
	@TableName nvarchar(255) = '',	-- this is the table to look at
	@FieldName nvarchar(255) = '',	-- this is the field that will be scanned
	@RTrim bit = 1,					-- should the field be right trimmed before finding the character set ... mostly for char field types
	@LowCharRange int = 0,			-- character scan start point
	@HighCharRange int = 255		-- character scan end point
as
begin
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	set nocount on;

	declare
		@count bigint = 0,
		@sql nvarchar(max) = '',
		@selects nvarchar(max) = '',
		@error varchar(200) = '',
		@field nvarchar(264) = '',
		@sqlbuild nvarchar(max) = ''
	declare @chars table(
		[character] nchar(1)
	)
	declare @statements table(
		[sql] varchar(max)
	)

	if @TableName = ''
	begin
		set @error = 'You must supply a table name.'
		raiserror (@error, 15, 1)
	end

	if @FieldName = ''
	begin
		set @error = 'You must supply a field name.'
		raiserror (@error, 15, 1)
	end
	
	if @error = ''
	begin
		-- Bracket the table name to avoid naming issues
		if left(@TableName, 1) <> '[' and right(@TableName, 1) <> ']'
		begin
			set @TableName = '[' + @TableName + ']'
		end
		
		-- Bracket the field name to avoid naming issues
		if left(@FieldName, 1) <> '[' and right(@FieldName, 1) <> ']'
		begin
			set @FieldName = '[' + @FieldName + ']'
		end
		
		-- Should we rtrim the field to deal with trailing spaces ... mostly for char fields
		if @RTrim = 1
		begin
			set @field = 'rtrim(' + @FieldName + ')'
		end
		
		-- Create a temp table to hold our examples of each character matched
		if object_id('tempdb..##fieldCharacterSetTemp') is not null
		begin
			drop table ##fieldCharacterSetTemp
		end
		set @sql = 'select * into ##fieldCharacterSetTemp from (select t1.* from ' + @TableSchema + '.' + @TableName + ' t1 inner join ' + @TableSchema + '.' + @TableName + ' t2 on 1 = 1 where 1 = 0) X'
		exec(@sql)

		--Loop through the ascii characters low to high
		while @LowCharRange <= @HighCharRange
		begin
			--Build the from and where clauses
			set @sqlbuild = ''
			set @sqlbuild = @sqlbuild + 'from '
			if @TableSchema <> ''
			begin
				set @sqlbuild = @sqlbuild + @TableSchema + '.'
			end
			set @sqlbuild = @sqlbuild + @TableName + nchar(13)
			set @sqlbuild = @sqlbuild + 'where ' + @field
			-- If the character is A-Z or a-z then case sensitive compare
			if (@LowCharRange between 65 and 90) or (@LowCharRange between 97 and 122)
			begin
				set @sqlbuild = @sqlbuild + ' COLLATE Latin1_General_CS_AS'
			end
			set @sqlbuild = @sqlbuild + ' like '
			-- Deal with a few special SQL characters
			if @LowCharRange = 37 -- % character
			begin
				set @sqlbuild = @sqlbuild + '''%\%%'' escape ''\''' + nchar(13)
			end
			else if @LowCharRange = 39 -- ' character
			begin
				set @sqlbuild = @sqlbuild + '''%''''%''' + nchar(13)
			end
			else if @LowCharRange = 95 -- _ character
			begin
				set @sqlbuild = @sqlbuild + '''%\_%'' escape ''\''' + nchar(13)
			end
			else
			begin
				set @sqlbuild = @sqlbuild + '''%' + nchar(@LowCharRange) + '%''' + nchar(13)
			end

			-- Buld main counting sql
			set @sql = ''
			set @sql = @sql + 'select @countOut = count(*)' + nchar(13)
			set @sql = @sql + @sqlbuild

			--Get count of rows that have the character
			execute sp_executesql @sql,N'@countOut bigint = 0 output',@countOut=@count output

			--If any rows have this character add it to the result set
			if @count > 0
			begin
				insert into @chars values (nchar(@LowCharRange))

				-- Build the select sql to look at example lines that have this character
				set @selects = ''
				set @selects = @selects + 'select top 1 *' + nchar(13)
				set @selects = @selects + @sqlbuild
				
				insert into @statements values (rtrim(replace(@selects, nchar(13), ' ')))

				set @sql = ''
				set @sql = @sql + 'insert into ##fieldCharacterSetTemp' +nchar(13)
				set @sql = @sql + @selects
				
				exec(@sql)
			end
			
			-- Next character
			set @LowCharRange = @LowCharRange + 1
		end

		select * from @chars
		select * from @statements
		if object_id('tempdb..##fieldCharacterSetTemp') is not null
		begin
			select * from ##fieldCharacterSetTemp
			drop table ##fieldCharacterSetTemp
		end
	end
end

Wednesday, September 14, 2011

Weary Mommy

Some days I feel I am losing the battle.  The kids are so demanding and there is little gratitude at this stage.  I just got both kids in bed.  I did Desmond’s routine first, but then he literally screamed and wailed for me while I tried getting Kaylynn down.  I find myself squeezing my eyes shut and taking a deep breath so I don’t flip out.  I don’t always succeed.  I got Kaylynn down, found the missing lambie responsible for the incessant screaming, and tried explaining to Desmond why I can’t always drop everything and run to him when he calls me.  I tried reassuring him that I hear him and always want to get him what he needs but sometimes he’ll have to wait a little bit.  (I’m sure he gets it now.)  Then Kaylynn was crying again so I fed her some more and got her down again.  I think her teeth are bothering her.  Anyway, it’s silent now and I think I should probably go to bed.

DSC_0020I’m feeling drained: physically and emotionally.  When I’m not dealing with kids I’m trying to do a load of laundry or dishes.  I feel I’m just here to clean up messes and take care of everyone else.  Many days I don’t even take enough time for myself to get out of my pajamas which sounds nice to stay in your pjs all day, right?  But then I feel so blah and gross.  It doesn’t help. 

I’m also tired of being overweight.  I need to find the time to get myself back in shape!  I think I’ll join a gym down the road and go at 5:30 in the morning before Davin needs to go to work so I can do it ALL BY MYSELF without any distractions from the kids or the endless housework.  The problem is I’ll have to go to bed around 9 so I can wake up at that hour.  (Yeah, right.)  Still I need to change something.  I hate starting every day with a sigh and a groan.  I know exercise will help me have more energy and feel better about myself.  This way I’ll have time to refocus, talk to God in the quiet of the morning, and get the day started off right.  I need to begin my day on a positive note before someone’s crying for me. I’ll have to sacrifice sleep to get “me” time, but when I start falling apart everyone suffers so I’ve got to do it.  God, please give me an extra measure of grace!

Sunday, September 11, 2011

September Birthdays at Oaks Park

2011-09-101

Saturday was spent at Oaks Amusement Park with my family celebrating Mom and Nathan’s birthdays.  They wanted to do something different this year so this was it.  We all had fun going on thrill rides and most of my pictures ended up being of the go carts.  Kaylynn slept about half the time, and Desmond went on lots of kiddie rides but some were too scary.  The big slide was too fast, but the Ferris wheel was slow and offered a fun view of the “race cars” down below.  A fun time was had by all, and we ended the very hot day with a delicious ice cream cone.

Friday, September 09, 2011

Growing, Playing, and Shopping

It’s hard to believe Kaylynn is four months old today!  Gotta get a cute picture of her. 

DSC_0004The other day Desmond enjoyed pulling Kaylynn around in his little wagon. It’s fun to see them playing together!  He is very careful with her and likes bringing her toys to play with.  Kaylynn likes to watch him and gives him lots of smiles.  Sometimes Desmond can even get her to laugh when I can’t.

Yesterday I checked Desmond into Playland at Fred Meyer and got so much shopping done while Kaylynn slept in the Ergo. Amazing! Why haven’t I used this free service before? Desmond had a great time playing with toys – which is what he begs me to do whenever we go shopping anyway. I’ll definitely be doing more shopping here. What a great feeling to be able to get everything on my list and not have to chop it down to the bare essentials so we can get out of the store without a meltdown.

I’m gearing up for the JBF kids consignment sale next week.  I’m tagging a few things to sell, and I’ll also be volunteering for one shift.  Hopefully I can make a bit of money to cover some of what I need to buy.  Kaylynn needs a few warm clothes for winter in a couple different sizes and Desmond needs pretty much everything.  I love shopping this sale for good deals; it feels like a treasure hunt.  It’s a bit time consuming though with three different trips for drop off, volunteering, and pick up, not to mention the hours needed to shop!

Friday, September 02, 2011

Things that Make Me Smile

DSC_0188

Kaylynn’s square toes!  All her toes are the same length and it’s really obvious when she has socks on.  Little rectangle feet!

DSC_0004

Playing trains with Desmond and getting to create a new track just about every day.

DSC_0032

The clean baby smell and pure, expressive baby eyes.

Oregon State Fair

2011-08-23

Last Saturday we went to the Oregon State Fair.  Davin had the idea to leave Kaylynn with my parents who were thrilled to watch her so it was a win-win.  It was nice to spend some time just with Desmond.  After having two, one feels so simple.

We enjoyed some kiddie rides though many of them were a bit too adventurous for Desmond even if I was with him.  His favorite was the blue dump truck and his least favorite was the little roller coaster.  I wish I had been quicker with the camera because his face was priceless!  His arms thrown wide against the seat and sheer terror on his face for his one time around the track.  “Danger” may be his middle name, but he wants to take things easy for now.

It was a perfect day except for the 90 degree heat.  We went to the indoor displays to cool down and enjoyed some ice cream in the shade.  A fun day for sure!

All to the Glory of God

The days can feel like such a rat race sometimes.  Every day starts by waking up to a kid crying or calling for me.  I’m usually very groggy and tired because I’ve been awake two or three times during my short night. 

We’re trying to help Kaylynn go back to sleep in the middle of the night without feeding her since we know she’s capable of sleeping through.  Last night we succeeded in postponing her 1am feeding until 3:30.  During the day I’m trying to help her sleep more consecutive hours, too.  She’ll usually only nap 45 minutes to an hour at a time.

DSC_0193I realized yesterday that an average day holds only two waking non-kid hours.  One is during naptime IF I can get them both down at the same time.  Desmond boycotts naps some days, but at least he’ll usually stay quietly on his bed for an hour.  The other hour is after we get Kaylynn to sleep around 9pm.  These times are also the only uninterrupted time I have to do dishes, fold laundry, or simply talk to Davin.  As a result I’m often up too late to get an extra half hour to relax, but then I start the next day in a deficit and the cycle continues.

Exercise has fallen by the wayside for now.  I just can’t pull myself out of bed any earlier and at the end of a long day I don’t feel like spending my final hour sweating.  Yesterday’s exercise was playing “Larryboy spaceship” with Desmond and doing crunches with Kaylynn on my tummy.  Oh well.  The scale is still moving in the right direction it’s just moving slowly.

DSC_0195

All that said, I’ve had a few refocus moments lately when I’ve heard of other families’ tragedies.  Yes, this is a physically hard phase with precious little time to recover for a new day, but it is a gift.  Some mothers would do anything to be able to hold their baby again – sleepless nights and all.  And I am shaping these lives for eternity.  Even the mundane tasks can be done to the glory of God.  Yes, even cleaning poop off the floor… again.