Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Convert alphanumeric to BigInt Expand / Collapse
Author
Message
Posted Tuesday, March 24, 2009 4:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
jchandramouli (3/23/2009)
Jeff,

I have used your script both in UDF and as a query. But the problem is when the table records exceeds 10000, there is a performance issue. It takes more than two mins.


I check it when I get home from work tonight. Thanks for the feedback.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #682949
Posted Tuesday, March 24, 2009 4:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
And, yes... there's a few more tricks we can try.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #682950
Posted Tuesday, March 24, 2009 4:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
One more thing.... can you post the code for the UDF that uses my method... and why are you using a UDF for this? It was meant to resolve the entire table at once.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #682952
Posted Tuesday, March 24, 2009 10:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
Wow.... you're right. I've never seen the XML concatenation method run so slow. Now that we have the test data you want, I believe we can do a little better than the While Loop solution you have.

Here's your test data generator (I did a little "Mo-dee-can tweekin' on it) and your test table (best I can make out from your code)...

--===== Create the test table and populate it
CREATE TABLE #Temp (Data VARCHAR(100), OutPut BIGINT)
INSERT INTO #Temp (Data)
SELECT TOP 20000 '~`!@#$%^&*()_2+-={}[]:";<>,.?/|\",.~`'
FROM Master.sys.SysColumns sc1
CROSS JOIN Master.sys.SysColumns sc2

Here's my idea of what the function should look like....
 CREATE FUNCTION dbo.DigitsOnly
(@SomeString VARCHAR(8000))
RETURNS BIGINT
AS
BEGIN
DECLARE @CleanString VARCHAR(8000)
SET @CleanString = ''
SELECT @CleanString = @CleanString + SUBSTRING(@SomeString,t.N,1)
FROM dbo.Tally t
WHERE t.N <= LEN(@SomeString)
AND SUBSTRING(@SomeString,t.N,1) LIKE '[0-9]'
RETURN CAST(@CleanString AS BIGINT)
END

Notice that it uses a Tally table... the following article explains not only how to build one, but also how it replaces a While Loop... it's got a heck of a lot more uses than just what show up in that article...

http://www.sqlservercentral.com/articles/TSQL/62867/

... And, here's the code to use the function...
SELECT * FROM #Temp
UPDATE #Temp SET Output = dbo.DigitsOnly(Data)
SELECT * FROM #Temp

Including the two selects, the While loop version takes between 9 and 10 seconds on my box. The Tally table version takes between 4 and 5.

Here's another test table you can run... same everything except different data...

drop table #Temp
go
--===== Create the test table and populate it
CREATE TABLE #Temp (Data VARCHAR(100), OutPut BIGINT)
INSERT INTO #Temp (Data)
SELECT TOP 20000 '~`!@#$%^&*()_2+-={}[]:";<>,.?/|\",.~`'+RIGHT(NEWID(),12)
FROM Master.sys.SysColumns sc1
CROSS JOIN Master.sys.SysColumns sc2

Both runs take about the same time as the prevously did with the Tally table version being almost twice as fast.

Hope all that helps.



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #683025
Posted Wednesday, March 25, 2009 12:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 1:27 AM
Points: 2,366, Visits: 1,837
Jeff Moden (3/24/2009)
Chirag (3/23/2009)
Jeff


select substring('1230-544',number,1)
from number where number <= len('1230-544')
and substring('1230-544',number,1) like '[%0-9%]'


Also, your query doesn't put it all back together like the op wanted and you don't need the % signs in the like because you're looking at a single character.


You are right.


"Keep Trying"
Post #683068
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse