Valid Email in Query

create function [dbo].[fn_isValidEmail](@string varchar(500))
returns int
as
begin
return (
 select
   case when @string LIKE '%_@_%_.__%'
     --AND @test NOT LIKE '%[]%'
   then 1
   else 0
   end
  ) 
end

Strip Non Alpha in Query

create function [dbo].[fn_stripNonAlpha](@temp varchar(1000))
returns varchar(1000)
as
begin
 while patIndex('%[^a-z]%', @temp) > 0
  set @temp = stuff(@temp, patIndex('%[^a-z]%', @temp), 1, '')

 return @temp
end

Strip Non Numerics in Query

create function[dbo].[fn_StripNonNumeric]( @string varchar(max))
returns varchar(max)
as
begin
if patindex('%[^0-9]%', @string) > 0
while patindex('%[^0-9]%', @string) > 0
set @string = stuff(@string, patindex('%[^0-9]%', @string), 1, '')
return @string
end

Replace Vanity Phone Numbers with Numbers

create function replacePhoneNumberVanityChars(@string varchar(max))
returns varchar(max)
begin

 declare @len as int = len(@string);
 declare @i as int = 1;
 declare @char as varchar(1) = null;

 while(@i<=@len)
 begin

  set @char = right(left(@string,@i),1);

  if(@char in ('A','B','C'))
   set @char = '2';
  if(@char in ('D','E','F'))
   set @char = '3';
  if(@char in ('G','H','I'))
   set @char = '4';
  if(@char in ('J','K','L'))
   set @char = '5';
  if(@char in ('M','N','O'))
   set @char = '6';
  if(@char in ('P','Q','R','S'))
   set @char = '7';
  if(@char in ('T','U','V'))
   set @char = '8'; 
  if(@char in ('W','X','Y','Z'))
   set @char = '9';
  
  set @string = left(@string,@i-1) + @char  + right(@string,len(@string)-@i);

  set @char = null;
  set @i = @i +1;

 end
 
 return @string;

end

SQL Multiple table constraint

I've always wanted to know how to do a constraint across tables and someone on StackOverflow did too apparently. Here's the link:

How do I create a multiple-table check constraint?t

Deleting Duplicate Bridge Records

When I was importing some data I mistakenly dup'd records into a bridge (many to many) table. I wanted to delete all but the first occurrence of the relationships for the comibination of ids (snowBoarderID,snowBoardID) and made use of the ROW_NUMBER() over in SQL again.

with data as(
 select
  snowBoarderID,
  snowBoardID,
  ROW_NUMBER() over (partition by snowBoarderID,snowBoardID by SnowboarderToSnowboardID asc ) as rownumber
 from SnowboarderToSnowboard
 where isActive = 1
)
select * 
--delete
from data
where rownumber > 1

SQL MAX() RecordID ordered by date

I had a task where I needed to get the most recent timesheet record for task. A task can have many instances on the timesheet. I made use of the ROW_NUMBER() over SQL statement to first order the records by date. Then I got the record where the recordNumber was equal to 1.

select 
 tt.taskTimesheetID
from(
 select 
  ROW_NUMBER() over (partition by tt.taskID order by tt.startTime asc, tt.CreateDate desc ) as recordNumber
  ,tt.taskTimesheetID
  ,tt.taskID
 from tasktimesheet as tt
 where tt.isactive = 1
  and tt.taskID = 5254
   
 ) as data 
inner join taskTimesheet as tt on tt.taskTimesheetID = data.taskTimesheetID
 and data.recordNumber = 1

SQL replace() blank stuff

Will never return 0

select replace('','',0)

Will return zero if the value is ''

select case when '' = '' then 0 else 1 end

SQL Selects ='s

Instead of writing a select in a query like this

select 
[user].id,
 [user].name,
 snowboard.name as snowboard
from [user]
 inner join snowboard on snowboard.id = [user].snowboard_id

try this

select 
 [user].id,
 [user].name,
 snowboard = snowboard.name
from [user]
 inner join snowboard on snowboard.id = [user].snowboard_id

It helps all the columns line up and read nicer.

HQL vs. SQL

I've been playing with HQL for awhile now and want to share my thoughts of it against SQL. Some developers are hesitant to switch to HQL because of the querying. I don't blame them. Writing HQL can be tougher than SQL. After fighting threw some HQL queries I have found that SQL still has its place in an HQL application. Here are some of my findings:

1. List pages and aggregates are faster and easier to write in SQL. Most of the time the data points that list pages and aggregates are going to be displayed are already hashed out and don't need to be changed much.

2. HQL is great for getting and setting simple data quickly. SQL takes too much energy and time to insert or get a piece of data. The SQL queries you write are not going to be flexible enough for the new fields people are going to request.

3. Some routines are more efficient to be ran in SQL. HQL can be slow and it brings back too much data sometimes.

With the above findings I have organized my code to do saves and single entity gets with HQL and lists and routines with SQL. With this combo I have found it to be quick to get and set the data I need. It does take knowledge of both HQL and SQL to do this, but the languages are very similar and the learner curve isn't too bad. Here is an earlier post about the similarities. I hope this brings down the hesitation for the SQL people to try and use HQL for what it's good for.

I would like to hear peoples thoughts on this matter. It seems to come up a lot in HQL conversations, so comment away.

CF9 computed properties

I wanted to make a computed property in CF9 just like you can on a sql table (computed column). Dan Vega had a nice how-to awhile back but it didn't show a complex example.

In my project I had a "project" class that had many ratings on it (Example: rating of 1-5, 1 being bad and 5 being great) and I wanted to have a computed property that gave me the average rating of a project. Here is what my hib file looks like for the "project" class.

<class entity-name="Project" lazy="true" name="cfc:myproject.app.model.Project" table="`Project`">
        <id name="id" type="int">
                <column length="10" name="ID" />
         <generator class="identity" />
        </id>
        <property name="name" type="string">
                <column name="Name" sql-type="varchar(max)" />
        </property>
  <property name="description" type="string">
        <column name="Description" sql-type="varchar(max)" />
        </property>
        <property type="float" 
                formula="(select (sum(r.rank)*100.0) / (count(pr.id) *100.0) from project as p inner join project_rating as pr on pr.project_id = p.id inner join rating as r on r.id = pr.rating_id where pr.project_id = id)" 
                name="rating"/>  
</class>

Some key things to point out:
1. The formula is a sql query not a hql query.
2. I wrapped my entire statement in (), don't know why but it worked.
3. You always start the sql statement with the table of the class you are working on. Reason being is when you get to the "where" clause notice this piece "pr.project_id = id". "id" is not aliased because it grabs the current id of the object that is running the formula against. This is how the query will only run for single object and not all of the other db records in the project table.
4. Alias every thing except the "where id" part.

I struggled through these above. Hope this helps someone.

MS SQL GUID lengths

Working with MS SQL and found out that errors with GUIDs are only thrown if the length of the GUID isn't met. The length of the GUID can be over the length of a regular GUID. This is kinda wierd because one would think this would error, but in fact MS SQL actually only will store the length of the GUID String up to the length of a normal GUID. Check out the examples below...

create table Snowboarder(
 id uniqueidentifier not null,
 name varchar(200) not null,
 primary key (id)
)

/*--will throw an error---*/
insert into Snowboarder(
 id,
 name
)values(
 '',
 'Blank GUID'
)

/*--will work---*/
insert into Snowboarder(
 id,
 name
)values(
 'D832DE4B-8453-4831-9505-6CA8CB4A4033',
 'Regular GUID'
)

/*--will work, but it will only store the first guid---*/
insert into Snowboarder(
 id,
 name
)values(
 'FE7A6068-080B-43B3-B392-3ABCE2E5FADF13725D91-B434-41B4-97A8-5607BF864EF5',
 'Two GUIDS together'
)

/*--will throw an error because the first part is already in the db--*/
insert into Snowboarder(
 id,
 name
)values(
 'D832DE4B-8453-4831-9505-6CA8CB4A40336448153D-A72F-42A8-BB35-17EE076D393C',
 'Two GUIDS together with the first part already in the db'
)

Select Top N Rows Per Group

I had an interesting sql task today. Someone asked me to get a max of three time-sheet entries per project. When an employee fills out a time-sheet they select a project for which the time is applied to. I was asked to grab a max of three of these entries and dump them in a csv for the accountant to audit. I was troubled on where to start this task. I am not an sql guy, so this was a challenge for me. I talked to the sql guys, but they were pretty busy today. One helped me but are minds together struggled to find the answer. After the googling, I tried a search result called "Cosmo Central" and found the anwser. Below is the piece that helped me so much...

with data as(
select row_number() over(partition by projects.name order by timesheet.id) as 'RowNumber’,
timesheet.id, projects.name
from timesheet
inner join projects on projects.id = timesheet.project_id
)

select id, name
from data
where RowNumber <= 3

Resource Used:
http://www.cosmocentral.com/2010/04/select-top-n-rows-per-group-ms-sql/

Yes. It looks like I copied the post and re-posted, but I am more so happy that I found the answer that I wanted to share the solution again. The credit still goes to Cosmo Central. Thanks a bundle.

From SQL to HQL

Most of the development I have done has been with SQL records. Now that CF9 is out I've started playing around with HQL objects and it has been a learning curve but for the best. I struggled and continue to struggle with writing HQL, because my mind still wants to do SQL. I wanted to share what I have learned so far.

Given the db schema below I will show how to write a piece in sql followed by a piece in hql.

There is a PRODUCT table with a many to many relationship with the SIZE table and the PRODUCT table has many to many relationship with the CATEGORY table.

PRODUCT -|-----= PRODUCT_SIZE =------|- SIZE
PRODUCT -|-----= PRODUCT_CATEGORY =-----|- CATEGORY

1. Get all the products
SQL

select * from product

HQL

from Product

2. Get all the products with price equal to $100.
SQL

select * from product where price = '100'

HQL

from Product where price = '100'

3. Get all the products with size_id = 1.
SQL

select * from product 
inner join product_size on product.id = product_size.product_id
where product_size.size_id = 1

HQL

select product
from Product product
join product.sizes size
where size.id = 1

4. Get all the products with size_id = 1 and category_id = 1.
SQL

select * from product 
inner join product_size on product.id = product_size.product_id
inner join product_category on product.id = product_category.product_id
where product_size.size_id = 1
and product_category.category_id = 1

HQL

select product
from Product product
where exists (
from product.categories category
where category.id = 1
)
and exists (
from product.sizes size
where size.id = 1
)

Note: HQL can't solve all queries, a good example is Accounting queries, but it does help with the majority of the light weight queries. I get the line "Well what's the point of using objects if records work fine.". I reply with "There is no point, but if you want to update all your queries with new properties, you will quickly find the point."

Resources used:

http://www.barneyb.com/barneyblog/2010/05/05/embrace-your-hsql/

Indexed views, not using your indexes

I was working on an export where we were building a query in a string to be passed to a CLR through a stored proc to a small .NET assembly on the db server and ran into some issues with performance. Now, of course CF is slow at running queries...that's why we passed to it .NET to handle the execution, but I didn't think .NET would be slow. I couldn't figure out why my query was slow for the life of me. All the indexes were place, no extra joins were added and the query still took 20 minutes to run. After talking to a DB developer he told me that even though we indexed the sql "views" the indexes were not being used. I quickly freaked out wondering why SQL didn't use the indexes on the views like it uses the indexes on the tables. It should be assumed. Turns out the reason was that, "It is just one of those things". To fix the issue we added "with noexpand" to the joins to the indexed view and then query time went down to about 8 seconds. Ridiculous.

Tell the join how to join.

I have been having a lot issues…with query execution plans lately (using CF and SQL). SQL has been choosing shitty plans thus making my query slow. If you have ever looked at execution plans, they are difficult read...unless you are a DB developer. I have had no idea wtf was going on with these plans…until now. Another developer sent this great link explaining what the joins are doing in the execution plan. You can then tell your joins in your query how to join.

Example:
select * from [user]
inner hash join enterprise_user on enterprise_user.user_id = [user].id

An execution plan does more than just join identification, such as indexes, but the join thingy is all I really cared about at the moment.

P.S. Only specify the “how to join” if you have to; if your query blows at running, let SQL try to calculate the best plan.

Use Case: Had a query in cfquery tags and it was choosing an execution plan with nested loop joins instead of the fast hash joins.

Select into from ColdFusion query object

So I got a question from a friend of mine who had a query returned in ColdFusion and wanted to do a "select into" with the query object. He found out how to do it one statement. Check it out.

<cfquery datasouce="ds">
SELECT COLUMN_1,COLUMN_2,COLUMN_3
INTO DATABASE_TABLE
FROM QUERYOBJECT.THEQUERY
</cfquery>

Default on bit column in sql.

alter table tablename
add constraint DF_tablename_columnname default ((0)) for columnname

cfqueryparam cf_sql_decimal scale default

A few months ago I was debugging some code that was rounding to the nearest int even though the data being entered was of decimal type. I checked the code to see if round() or numberformat() was being used, but they weren't. I started throwing dumps in the cfquery and noticed that before the decimal var entered in the cfqueryparam it was a decimal, but when the query was ran it was of type int. I was totally confused on how the number was being formatted. Turns out there is a scale attribute I was unaware of on cfqueryparam. The scale attribute is defaulted to 0 and is only applicable to cf_sql_numeric and cf_sql_decimal. I think it's kinda pointless for cf_sql_decimal to have zero decimals. Anyways I ended up adding scale to cfqueryparam with a value of 2 and fixed the issue.

I can see why scale of zero would make sense for cf_sql_numeric but not for cf_sql_decimal.