Dealing with layered function calls

5

February 5, 2015 by Kenneth Fisher

T-SQL is not the best language in the world for formatting strings. It can be done, but typically you are going to end up with lots of function calls. Let’s say I want to output the Rate as a string 5 characters wide padded with 0’s on the left. So 4.25 becomes 00425. Here is one way to do this. It may not be the best way but it’s perfect for this demonstration.

DECLARE @Rate decimal(10,2)
SET @Rate = 4.25
SELECT RIGHT('00000' + 
	REPLACE(CAST(CAST(ROUND(@Rate, 0) AS INT)) AS VARCHAR(20)),'.',''),5)

Now depending on how much expereince you have with things like this you may or may not have noticed I have a bug in the code. (Yes deliberatly.) So how do we find it?

First here’s the error

Msg 1035, Level 15, State 10, Line 3
Incorrect syntax near 'CAST', expected 'AS'.

Well I have two CASTs and two AS clauses so why the error? Try laying it out like this:

DECLARE @Rate decimal(10,2)
SET @Rate = 4.25
SELECT
	RIGHT('00000' + 
		REPLACE(
				CAST(
					CAST(
						ROUND(
							@Rate
						, 0)
					AS INT)
				 ) -- <-
		AS VARCHAR(20))
	,'.','')
,5)

Well that’s a bit easier to see. There’s an extra ). So let’s remove that.

DECLARE @Rate decimal(10,2)
SET @Rate = 4.25
SELECT
	RIGHT('00000' + 
		REPLACE(
				CAST(
					CAST(
						ROUND(
							@Rate
						, 0)
					AS INT)
			AS VARCHAR(20))
		,'.','')
	,5)

Well at least that doesn’t return an error. It does however return 00005 which is not exactly what we want. But with this new format we can easily test out some options. I think it’s the ROUND() that’s the problem so let’s comment that out.

DECLARE @Rate decimal(10,2)
SET @Rate = 4.25
SELECT
	RIGHT('00000' + 
		REPLACE(
				CAST(
					CAST(
--						ROUND(
							@Rate
--						, 0)
					AS INT)
			AS VARCHAR(20))
		,'.','')
	,5)

No change. Let’s try getting rid of the CAST AS INT as well.

DECLARE @Rate decimal(10,2)
SET @Rate = 4.25
SELECT
	RIGHT('00000' + 
		REPLACE(
				CAST(
--					CAST(
--						ROUND(
							@Rate
--						, 0)
--					AS INT)
			AS VARCHAR(20))
		,'.','')
	,5)

And that’s what we want! Now it’s no big deal to delete the four commented out lines and condense the whole thing back down.

DECLARE @Rate decimal(10,2)
SET @Rate = 4.25
SELECT RIGHT('00000' + REPLACE(CAST(@Rate AS VARCHAR(20)),'.',''),5)

Generally I recommend leaving it condensed down like this when in production (or just not being worked on) and then break it out when you need to figure out a problem or modify something.

Hope this helps!

5 thoughts on “Dealing with layered function calls

  1. Corey Lawson says:

    Replace(replace(str(5.25, 10, 2), ‘0’, ‘.’,”)

    • Fair enough. Although I did say it was just an example to demonstrate lots of layered functions 🙂

      Funnily enough I ended up using the same technique to fix it though 🙂

      replace(replace(str(5.25, 10, 2), ‘ ‘,’0’), ‘.’,”)

  2. Henry Stinson, BSECE says:

    Or you could have multiplied rate by 100 and either rounded off or CAST AS INT (latter faster).

  3. […] About Patching SQL Server Availability Groups (Hotfixes, Cumulative Updates, and Service Packs) Dealing with layered function calls #vBrownBag DevOps Follow-Up PowerShell DSC with Steven Murawski (@StevenMurawski) Tip # 4 – Not […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,664 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: