Intresting Query

Question

Post reply

Intresting Query

vs.satheesh

SSCrazy

Points: 2392
More actions
August 6, 2012 at 7:55 am

#145546

Hi All
I am facing Following interview Question.Its very intresting. I have one table that table only two columns in following manner
Sno(int) Column(varchar(20)
1 01112300
2 00100
3 01234567800
4 00023456800
5 012005670
6 0030560
In this above table i want remove lelft zero and right zero.i want following result set
1 11123
2 1
3 12345678
4 234568
5 1200567
6 3056
Please help me.

Viewing 15 posts - 1 through 15 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

DECLARE @t TABLE(Sno int,Col varchar(20))

INSERT INTO @t(Sno,Col)

SELECT 1, '01112300' UNION ALL

SELECT 2, '00100' UNION ALL

SELECT 3, '01234567800' UNION ALL

SELECT 4, '00023456800' UNION ALL

SELECT 5, '012005670' UNION ALL

SELECT 6, '0030560';

SELECT Sno,

REPLACE(LTRIM(RTRIM(REPLACE(Col,'0',' '))),' ','0')

FROM @t

ORDER BY Sno;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

vs.satheesh (8/6/2012)
Hi All
I am facing Following interview Question.Its very intresting. I have one table that table only two columns in following manner
Sno(int) Column(varchar(20)
1 01112300
2 00100
3 01234567800
4 00023456800
5 012005670
6 0030560
In this above table i want remove lelft zero and right zero.i want following result set
1 11123
2 1
3 12345678
4 234568
5 1200567
6 3056
Please help me.

deleted...didnt read q properly !

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

My version: -

--Create sample data

SELECT [Sno], [Column]

INTO #sampleData

FROM (SELECT [Sno] = 1, [Column] = '01112300'

UNION ALL SELECT 2, '00100'

UNION ALL SELECT 3, '01234567800'

UNION ALL SELECT 4, '00023456800'

UNION ALL SELECT 5, '012005670'

UNION ALL SELECT 6, '0030560') a;

--Actual solution

SELECT [Sno], [Column] AS originalData,

CAST(REVERSE(CAST(REVERSE([Column]) AS INT)) AS INT) AS newData

FROM #sampleData;

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

Quick performance test: -

SET NOCOUNT ON;

IF object_id('tempdb..#sampleData') IS NOT NULL

BEGIN

DROP TABLE #sampleData;

END;

--1,000,000 Random rows of data

SELECT TOP 1000000 IDENTITY(INT,1,1) AS [Sno],

CAST((ABS(CHECKSUM(NEWID())) % 20000) + 1 AS VARCHAR(20)) AS [Column]

INTO #sampleData

FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;

DECLARE @HOLDER INT;

PRINT REPLICATE('=',80);

PRINT 'CADAVRE';

PRINT REPLICATE('=',80);

SET STATISTICS TIME ON;

SELECT @HOLDER = CAST(REVERSE(CAST(REVERSE([Column]) AS INT)) AS INT)

FROM #sampleData;

SET STATISTICS TIME OFF;

PRINT REPLICATE('=',80);

PRINT 'MARK';

PRINT REPLICATE('=',80);

SET STATISTICS TIME ON;

SELECT @HOLDER = REPLACE(LTRIM(RTRIM(REPLACE([Column],'0',' '))),' ','0')

FROM #sampleData;

SET STATISTICS TIME OFF;

================================================================================

CADAVRE

================================================================================

SQL Server Execution Times:

CPU time = 531 ms, elapsed time = 525 ms.

================================================================================

MARK

================================================================================

SQL Server Execution Times:

CPU time = 1937 ms, elapsed time = 1942 ms.

Ah, but wait. Mark forgot the replace bug 🙂

Change his code to this: -

SELECT Sno,

REPLACE(LTRIM(RTRIM(REPLACE(Col COLLATE Latin1_General_BIN2,'0',' '))),' ','0')

FROM @t

ORDER BY Sno;

Now let's have another look: -

SET NOCOUNT ON;

IF object_id('tempdb..#sampleData') IS NOT NULL

BEGIN

DROP TABLE #sampleData;

END;

--1,000,000 Random rows of data

SELECT TOP 1000000 IDENTITY(INT,1,1) AS [Sno],

CAST((ABS(CHECKSUM(NEWID())) % 20000) + 1 AS VARCHAR(20)) AS [Column]

INTO #sampleData

FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;

DECLARE @HOLDER INT;

PRINT REPLICATE('=',80);

PRINT 'CADAVRE';

PRINT REPLICATE('=',80);

SET STATISTICS TIME ON;

SELECT @HOLDER = CAST(REVERSE(CAST(REVERSE([Column]) AS INT)) AS INT)

FROM #sampleData;

SET STATISTICS TIME OFF;

PRINT REPLICATE('=',80);

PRINT 'MARK';

PRINT REPLICATE('=',80);

SET STATISTICS TIME ON;

SELECT @HOLDER = REPLACE(LTRIM(RTRIM(REPLACE([Column],'0',' '))),' ','0')

FROM #sampleData;

SET STATISTICS TIME OFF;

PRINT REPLICATE('=',80);

PRINT 'MARK''S IMPROVED';

PRINT REPLICATE('=',80);

SET STATISTICS TIME ON;

SELECT @HOLDER = REPLACE(LTRIM(RTRIM(REPLACE([Column] COLLATE Latin1_General_BIN2,'0',' '))),' ','0')

FROM #sampleData;

SET STATISTICS TIME OFF;

Results this time: -

================================================================================

CADAVRE

================================================================================

SQL Server Execution Times:

CPU time = 515 ms, elapsed time = 513 ms.

================================================================================

MARK

================================================================================

SQL Server Execution Times:

CPU time = 1891 ms, elapsed time = 1904 ms.

================================================================================

MARK'S IMPROVED

================================================================================

SQL Server Execution Times:

CPU time = 609 ms, elapsed time = 607 ms.

--edit--

Link to replace "bug" here.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Dwain Camps SSC Guru Points: 86948 More actions · Answer 5

Interesting query, interesting test results.

Cadavre - Try changing your @Holder as follows:

DECLARE @HOLDER VARCHAR(20);

What do the results say then?

Of course its easy being a critic. 🙂

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Dwain Camps SSC Guru Points: 86948 More actions · Answer 6

I also played around with your test harness to ensure there were some leading and trailing 0s.

SET NOCOUNT ON;

IF object_id('tempdb..#sampleData') IS NOT NULL

BEGIN

DROP TABLE #sampleData;

END;

--1,000,000 Random rows of data

SELECT TOP 1000000 IDENTITY(INT,1,1) AS [Sno],

'0000' + CAST((ABS(CHECKSUM(NEWID())) % 20000) + 1 AS VARCHAR(20)) + '0' AS [Column]

INTO #sampleData

FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;

--DECLARE @HOLDER VARCHAR(20);

DECLARE @HOLDER INT;

PRINT REPLICATE('=',80);

PRINT 'CADAVRE';

PRINT REPLICATE('=',80);

SET STATISTICS TIME ON;

SELECT @HOLDER = CAST(REVERSE(CAST(REVERSE([Column]) AS INT)) AS INT)

FROM #sampleData;

SET STATISTICS TIME OFF;

PRINT REPLICATE('=',80);

PRINT 'MARK';

PRINT REPLICATE('=',80);

SET STATISTICS TIME ON;

SELECT @HOLDER = REPLACE(LTRIM(RTRIM(REPLACE([Column],'0',' '))),' ','0')

FROM #sampleData;

SET STATISTICS TIME OFF;

PRINT REPLICATE('=',80);

PRINT 'MARK''S IMPROVED';

PRINT REPLICATE('=',80);

SET STATISTICS TIME ON;

SELECT @HOLDER = REPLACE(LTRIM(RTRIM(REPLACE([Column] COLLATE Latin1_General_BIN2,'0',' '))),' ','0')

FROM #sampleData;

SET STATISTICS TIME OFF;

PRINT REPLICATE('=',80);

PRINT 'DWAIN';

PRINT REPLICATE('=',80);

SET STATISTICS TIME ON;

SELECT @HOLDER = 1*REPLACE(RTRIM(REPLACE([Column] COLLATE Latin1_General_BIN2, '0', ' ')), ' ', '0')

FROM #sampleData

;

SET STATISTICS TIME OFF;

On one run I got this (mine wins):

================================================================================

CADAVRE

================================================================================

SQL Server Execution Times:

CPU time = 4462 ms, elapsed time = 9067 ms.

================================================================================

MARK

================================================================================

SQL Server Execution Times:

CPU time = 4805 ms, elapsed time = 9066 ms.

================================================================================

MARK'S IMPROVED

================================================================================

SQL Server Execution Times:

CPU time = 4867 ms, elapsed time = 8856 ms.

================================================================================

DWAIN

================================================================================

SQL Server Execution Times:

CPU time = 3744 ms, elapsed time = 7037 ms.

On another run I got this (CADAVRE's wins):

================================================================================

CADAVRE

================================================================================

SQL Server Execution Times:

CPU time = 4430 ms, elapsed time = 8580 ms.

================================================================================

MARK

================================================================================

SQL Server Execution Times:

CPU time = 5850 ms, elapsed time = 10912 ms.

================================================================================

MARK'S IMPROVED

================================================================================

SQL Server Execution Times:

CPU time = 5242 ms, elapsed time = 10125 ms.

================================================================================

DWAIN

================================================================================

SQL Server Execution Times:

CPU time = 5195 ms, elapsed time = 9211 ms.

I'm thinking the winner has more to do with the number of zeroes replaced because I got quite a variation on results for this.

For overall terseness, I kinda like this one, even though it doesn't win the performance tests 😛

REVERSE(1*REVERSE([Column] * 1))

Although it does seem to be a pretty serious contender when @Holder is VARCHAR(20), and may even be the overall winner across 10-20 runs of different random test data!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 7

REVERSE(1*REVERSE([Column] * 1))

Dwain - elegant but doesn't work for larger values. Example:

DECLARE @Foo VARCHAR(20)

SET @Foo = '00002432002000'

SET @Foo = REVERSE(1*REVERSE(@foo * 1))

PRINT @Foo

Output:

Msg 248, Level 16, State 1, Line 3

The conversion of the varchar value '00002432002000' overflowed an int column. Maximum integer value exceeded.

00002432002000

🙁

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Dwain Camps SSC Guru Points: 86948 More actions · Answer 8

derek.colley (8/10/2012)
REVERSE(1*REVERSE([Column] * 1))
Dwain - elegant but doesn't work for larger values. Example:
DECLARE @Foo VARCHAR(20)
SET @Foo = '00002432002000'
SET @Foo = REVERSE(1*REVERSE(@foo * 1))
PRINT @Foo
Output:
Msg 248, Level 16, State 1, Line 3
The conversion of the varchar value '00002432002000' overflowed an int column. Maximum integer value exceeded.
00002432002000
🙁

Hehe... I know. But then again neither does Cadavre's:

DECLARE @Foo VARCHAR(20)

SET @Foo = '00002432002000'

SET @Foo = CAST(REVERSE(CAST(REVERSE(@foo) AS INT)) AS INT)

PRINT @Foo

Output: Same error message as you posted for mine.

Of course, his could always be changed to CAST to BIGINT.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 9

And if the BIGINT is not big enough for you, use DECIMAL(38)

It can handle numbers upto:

99,999,999,999,999,999,999,999,999,999,999,999,999

🙂

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Dwain Camps SSC Guru Points: 86948 More actions · Answer 10

Eugene Elutin (8/10/2012)
And if the BIGINT is not big enough for you, use DECIMAL(38)
It can handle numbers upto:
99,999,999,999,999,999,999,999,999,999,999,999,999
🙂

Curious what that would do to the performance, but I'll let Cadavre tell us. 🙂

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 11

If take it a bit more serious, you better to be careful...

The given definition of the table has Column(varchar(20). Is any guarantee that the values there will contain only digits?

From my experience I can tell, if some unexpected value is allowed by the table design, you will sooner or later get it there!

Therefore, I wouldn't use logic which converts varchar values to numbers without validation. So, looks like REPLACE would be more suitable for this one as it will not produce run-time conversion error if the bad data ends in the table...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Dwain Camps SSC Guru Points: 86948 More actions · Answer 12

Eugene Elutin (8/10/2012)
If take it a bit more serious, you better to be careful...
The given definition of the table has Column(varchar(20). Is any guarantee that the values there will contain only digits?
From my experience I can tell, if some unexpected value is allowed by the table design, you will sooner or later get it there!
Therefore, I wouldn't use logic which converts varchar values to numbers without validation. So, looks like REPLACE would be more suitable for this one as it will not produce run-time conversion error if the bad data ends in the table...

What you say is true, of course we could hope that the front end would take care of such cleansing issues.

The OP did say that this was an interview question, so hopefully it's no sitting in someone's production server waiting to wreak havoc. 🙂

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 13

...
What you say is true, of course we could hope that the front end would take care of such cleansing issues.
...

If cleansing is done in front-end, it would be better to remove leading and trailing zeros there too... 😎

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Cadavre SSC-Forever Points: 41690 More actions · Answer 14

dwain.c (8/10/2012)
I'm thinking the winner has more to do with the number of zeroes replaced because I got quite a variation on results for this.
For overall terseness, I kinda like this one, even though it doesn't win the performance tests 😛
REVERSE(1*REVERSE([Column] * 1))
Although it does seem to be a pretty serious contender when @Holder is VARCHAR(20), and may even be the overall winner across 10-20 runs of different random test data!

When I run your test harness, I get pretty consistent results: -

================================================================================

CADAVRE

================================================================================

SQL Server Execution Times:

CPU time = 641 ms, elapsed time = 643 ms.

================================================================================

MARK

================================================================================

SQL Server Execution Times:

CPU time = 3563 ms, elapsed time = 3602 ms.

================================================================================

MARK'S IMPROVED

================================================================================

SQL Server Execution Times:

CPU time = 921 ms, elapsed time = 924 ms.

================================================================================

DWAIN

================================================================================

SQL Server Execution Times:

CPU time = 1234 ms, elapsed time = 1241 ms.

Running this: -

SET NOCOUNT ON;

IF object_id('tempdb..#sampleData') IS NOT NULL

BEGIN

DROP TABLE #sampleData;

END;

--1,000,000 Random rows of data

SELECT TOP 1000000 IDENTITY(INT,1,1) AS [Sno],

'0000' + CAST((ABS(CHECKSUM(NEWID())) % 20000) + 1 AS VARCHAR(20)) + '0' AS [Column]

INTO #sampleData

FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;

--DECLARE @HOLDER VARCHAR(20);

DECLARE @HOLDER INT;

PRINT REPLICATE('=',80);

PRINT 'CADAVRE';

PRINT REPLICATE('=',80);

SET STATISTICS TIME ON;

SELECT @HOLDER = CAST(REVERSE(CAST(REVERSE([Column]) AS INT)) AS INT)

FROM #sampleData;

SET STATISTICS TIME OFF;

PRINT REPLICATE('=',80);

PRINT 'MARK';

PRINT REPLICATE('=',80);

SET STATISTICS TIME ON;

SELECT @HOLDER = REPLACE(LTRIM(RTRIM(REPLACE([Column],'0',' '))),' ','0')

FROM #sampleData;

SET STATISTICS TIME OFF;

PRINT REPLICATE('=',80);

PRINT 'MARK''S IMPROVED';

PRINT REPLICATE('=',80);

SET STATISTICS TIME ON;

SELECT @HOLDER = REPLACE(LTRIM(RTRIM(REPLACE([Column] COLLATE Latin1_General_BIN2,'0',' '))),' ','0')

FROM #sampleData;

SET STATISTICS TIME OFF;

PRINT REPLICATE('=',80);

PRINT 'DWAIN';

PRINT REPLICATE('=',80);

SET STATISTICS TIME ON;

SELECT @HOLDER = 1*REPLACE(RTRIM(REPLACE([Column] COLLATE Latin1_General_BIN2, '0', ' ')), ' ', '0')

FROM #sampleData

;

SET STATISTICS TIME OFF;

Running it with @HOLDER as a VARCHAR(20), also consistent: -

================================================================================

CADAVRE

================================================================================

SQL Server Execution Times:

CPU time = 750 ms, elapsed time = 759 ms.

================================================================================

MARK

================================================================================

SQL Server Execution Times:

CPU time = 3578 ms, elapsed time = 3607 ms.

================================================================================

MARK'S IMPROVED

================================================================================

SQL Server Execution Times:

CPU time = 890 ms, elapsed time = 885 ms.

================================================================================

DWAIN

================================================================================

SQL Server Execution Times:

CPU time = 1329 ms, elapsed time = 1349 ms.

Eugene Elutin (8/10/2012)
If take it a bit more serious, you better to be careful...
The given definition of the table has Column(varchar(20). Is any guarantee that the values there will contain only digits?
From my experience I can tell, if some unexpected value is allowed by the table design, you will sooner or later get it there!
Therefore, I wouldn't use logic which converts varchar values to numbers without validation. So, looks like REPLACE would be more suitable for this one as it will not produce run-time conversion error if the bad data ends in the table...

Possibly, yes. I took it as more of an intellectual challenge than anything as I've never ended up with data where I've wanted to lose characters in my database.

dwain.c (8/10/2012)
Eugene Elutin (8/10/2012)
And if the BIGINT is not big enough for you, use DECIMAL(38)
It can handle numbers upto:
99,999,999,999,999,999,999,999,999,999,999,999,999
🙂
Curious what that would do to the performance, but I'll let Cadavre tell us. 🙂

Makes the replace function way faster, I'd imagine.

SET NOCOUNT ON;

IF object_id('tempdb..#sampleData') IS NOT NULL

BEGIN

DROP TABLE #sampleData;

END;

--1,000,000 Random rows of data

SELECT TOP 1000000 IDENTITY(INT,1,1) AS [Sno],

'0000' + CAST((ABS(CHECKSUM(NEWID())) % 20000) + 1 AS VARCHAR(38))+ CAST((ABS(CHECKSUM(NEWID())) % 20000) + 1 AS VARCHAR(38))

+ CAST((ABS(CHECKSUM(NEWID())) % 20000) + 1 AS VARCHAR(38))+ CAST((ABS(CHECKSUM(NEWID())) % 20000) + 1 AS VARCHAR(38)) AS [Column]

INTO #sampleData

FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;

DECLARE @HOLDER VARCHAR(38);

--DECLARE @HOLDER INT;

PRINT REPLICATE('=',80);

PRINT 'CAST AS NUMERIC';

PRINT REPLICATE('=',80);

SET STATISTICS TIME ON;

SELECT @HOLDER = CAST(REVERSE(CAST(REVERSE([Column]) AS DECIMAL(38))) AS DECIMAL(38))

FROM #sampleData;

SET STATISTICS TIME OFF;

PRINT REPLICATE('=',80);

PRINT 'REPLACE TRIM';

PRINT REPLICATE('=',80);

SET STATISTICS TIME ON;

SELECT @HOLDER = REPLACE(LTRIM(RTRIM(REPLACE([Column] COLLATE Latin1_General_BIN2,'0',' '))),' ','0')

FROM #sampleData;

SET STATISTICS TIME OFF;

================================================================================

CAST AS NUMERIC

================================================================================

SQL Server Execution Times:

CPU time = 1953 ms, elapsed time = 1959 ms.

================================================================================

REPLACE TRIM

================================================================================

SQL Server Execution Times:

CPU time = 1265 ms, elapsed time = 1261 ms.

Eugene Elutin (8/10/2012)
You should try CLR too...

I may knock one up on my break 😉

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/