Tag Archives: SQL

SQL Saturday 370 – Phoenix

Thank you to everyone who attended my session – Make Your DBA Happy: 5 Habits Developers Can Implement Today.  If you missed the session you can watch the pre-recorded video (from a prior SQL Saturday) here.  

The slide deck is available here.  Enjoy!

Eric Oszakiewski

Eric Oszakiewski is a professional software developer based in Scottsdale, AZ with over 37 years of IT experience, and 19 years Native American Gaming experience. He is currently working as a .Net/SharePoint Development Lead for General Motors.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInYouTube

SQL Saturday #279 – Phoenix

Here is the video from my SQL Saturday #279 session on developer best practices & good habits to help DBAs keep the server healthy and secure. Please excuse the fact I was dealing with a sinus & chest infection during the presentation, then halfway through the camera died & we had to switch to Lauri’s Windows Phone…such a superior product! You’ll notice halfway through the presentation the resolution changes, and I apologize. Next time I do a speaking engagement I plan to use a tripod & better camera (or her phone again!)

During the presentation a good question came up regarding the effort required to manage SQL code in a source controlled application vs. in a stored procedure or view, and how I suggested keeping the code in source control can be perceived as a pain in the butt. In no way am I indicating source control is a pain. Source control is a necessary tool in the development process, that in my opinion should be used regularly by all developers regardless of platform or language. My goal is to compare the effort required when a simple change to a data type (for example) is needed, to check the project out, hunt for every location in the code where someone has hard-coded queries or statements that need to be changed, change those queries or statements (and hope you got them all), run unit tests, build/compile your application (assuming nothing else was negatively affected), deploy to test, send to QC for testing, deal with any other “issues” they find, then check the final build back into source control and release to prod, then sit in hypercare hoping nothing else happens…or simply have the DBA look in your stored procedure or view for any changes needed and change them immediately. The end point was it’s way easier to keep these in stored procs or views, not to mention it gives a centrally managed place for your database code that encourages communication between the developer and the DBA, making the entire team more effective. Sorry for any confusion.

Anyway, here’s the link to the slide deck for following along, as well as the presentation

Eric Oszakiewski

Eric Oszakiewski is a professional software developer based in Scottsdale, AZ with over 37 years of IT experience, and 19 years Native American Gaming experience. He is currently working as a .Net/SharePoint Development Lead for General Motors.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInYouTube

SharePoint Saturday Phoenix 2013

If you attended my session on Connecting a SharePoint list to a SQL table for CRUD ops, thank you for coming! If you missed it, or if you wanted to review it again, my girlfriend was kind enough to record it for everyone! The projector can be difficult to see due to room lighting, but you can always follow along with the slide deck. Both are included in this post:

Slide deck

Eric Oszakiewski

Eric Oszakiewski is a professional software developer based in Scottsdale, AZ with over 37 years of IT experience, and 19 years Native American Gaming experience. He is currently working as a .Net/SharePoint Development Lead for General Motors.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInYouTube

Get the first occurrence of a character in SQL

(Originally posted September 2, 2009)
When I need to know the first (or any) occurrence of a character, I’m used to saying something like InStr() for VB or ASP, or String.IndexOf() in .Net, but when I tried to do something like this in SQL for a substring portion of a query I was creating, those obviously aren’t available. In Oracle InStr is available, but we`re talking about SQL.

To get the first occurrence of a character in a TEXT field (emphasis mine, because it must be a text compliant field) you use the PATINDEX() function. PATINDEX looks for the first occurrence of a specified pattern in a text field. Meaning, the field can be a text, nvarchar, char, etc. It cannot be an image or int, for example. PATINDEX, I learned, is typically used in BLOBs (Binary Large OBjects).  Let’s say you are querying a table called MYTABLE, looking for the pattern “forest” in the field STORY_DESCRIPTION. Your query would look like this:

SELECT PATINDEX(‘%,forest%’, STORY_DESCRIPTION)
FROM MYTABLE

The result would be the char position of the first occurrence of the pattern “forest”. This helped me when I was trying to put together something like this:

SELECT SUBSTRING(CN, 3, PATINDEX(‘%CN=’, CN) – 3)
FROM AD_TABLE

Where in this case I was querying an Active Directory results table and the CN field always started with “CN=”, but I wanted just the value from each row. This worked perfectly.
Although those of you who are SQL experts know of a much simpler method, I’m sure, this one worked for me!

Eric Oszakiewski

Eric Oszakiewski is a professional software developer based in Scottsdale, AZ with over 37 years of IT experience, and 19 years Native American Gaming experience. He is currently working as a .Net/SharePoint Development Lead for General Motors.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInYouTube

Get first occurance of a character in SQL

When I need to know the first (or any)
occurrence of a character, I’m used to saying something like InStr() for VB or
ASP, or String.IndexOf() in .Net, but when I tried to do something like this
today (Sept 2009) in SQL for a substring portion of a query I was creating, those obviously
aren’t available. Well, in Oracle InStr is available I understand, but we`re
talking about SQL.
To get the first
occurrence of a character in a TEXT field (emphasis mine, because it must be a
text compliant field) you use the PATINDEX() function. PATINDEX looks for the
first occurrence of a specified pattern in a text field. Meaning, the field can
be a text, nvarchar, char, etc. It cannot be an image or int, for example.
PATINDEX, I learned, is typically used in BLOBs (Binary Large
OBjects).

Let’s say you are
querying a table called MYTABLE, looking for the pattern “forest” in the field
STORY_DESCRIPTION. Your query would look like this:

SELECT PATINDEX('%,forest%', STORY_DESCRIPTION)
FROM MYTABLE

The result would be the
char position of the first occurrence of the pattern “forest”. This helped me
when I was trying to put together something like this:

SELECT SUBSTRING(CN, 3, PATINDEX('%CN=', CN)-3)
FROM AD_TABLE

Where in this case I
was querying an Active Directory results table and the CN field always started
with “CN=”, but I wanted just the value from each row. This worked perfectly.
Although those of you who are SQL experts know of a much simpler method, I’m
sure, this one worked for me!

Eric Oszakiewski

Eric Oszakiewski is a professional software developer based in Scottsdale, AZ with over 37 years of IT experience, and 19 years Native American Gaming experience. He is currently working as a .Net/SharePoint Development Lead for General Motors.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInYouTube