converting to INT from varchar

Question

Post reply

converting to INT from varchar

Viewing 8 posts - 16 through 22 (of 22 total)

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

MyDoggieJessie (11/7/2011)

Hey Sean, does Jeff send you some sort of royalty checks in the mail from time-to-time? 😉

(heh, heh)

Read a few of Jeff's articles mate, see for yourself why he's so highly valued around here 😎

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

MyDoggieJessie SSC-Forever Points: 44461 More actions · Answer 2

I'm very familiar with Jeff, Gail, and many countless others. My comment was more of a personal joke to Sean because a few weeks back he made a comment to Jeff in a similar post that referenced the tally table...

It was not meant any other way, just in good fun :hehe: I have the utmost respect for everyone on this forum...

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Sean Lange (11/7/2011)

ryvlad (11/7/2011)

hi I found this function somewhere on web and it have been very helpful 🙂

just run it on your DB and you can always reference it from anywhere

You should look at the article by Jeff that I linked several posts ago. Or you can find it in my signature. A loop is not the best approach for splitting strings. It is slow and inefficient. Read that article and look at the performance differences he discusses. He talks about a number of ways of splitting strings and other than parsing xml that type of splitter is the worst performer of them all.

Check's in the mail... 😉

--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".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

ryvlad SSC Enthusiast Points: 162 More actions · Answer 4

Hi Jeff,

nice splitter function thanks.

I run it against some of my data and it works consistently faster than the one I have.

not by much, about 13 to 16 milliseconds faster, but still faster 🙂

also I made a little modification to it.

I understand that you made the string parameter 8000 char long for backward compatibility, but I needed a bigger value so I changed it to (MAX)

also the last substring() I removed the hardcoded 8000 parameter and replaced it with DATALENGTH(@pString)

do you think it is a bad idea?

/*

curtesy of Jeff Moden, 2011/05/02

Tally OH! An Improved SQL 8K “CSV Splitter” Function

"http://qa.sqlservercentral.com/articles/Tally+Table/72993/"

-- changed @pString to be VARCHAR(MAX) instead of (8000)

-- change Final Substring to use DATALENGTH(@pString) and not hardcoded 8000

*/

ALTER FUNCTION fn_Split2

--===== Define I/O parameters

(@pString VARCHAR(MAX), @pDelimiter CHAR(1))

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...

-- enough to cover VARCHAR(8000)

WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), --10E+1 or 10 rows

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front

-- for both a performance gain and prevention of accidental "overruns"

SELECT 0 UNION ALL

SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

),

cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)

SELECT t.N+1

FROM cteTally t

WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)

)

--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.

SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),

Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,DATALENGTH(@pString)))

FROM cteStart s

;

Sean Lange SSC Guru Points: 286562 More actions · Answer 5

Jeff Moden (11/7/2011)

Sean Lange (11/7/2011)

ryvlad (11/7/2011)

hi I found this function somewhere on web and it have been very helpful 🙂

just run it on your DB and you can always reference it from anywhere

You should look at the article by Jeff that I linked several posts ago. Or you can find it in my signature. A loop is not the best approach for splitting strings. It is slow and inefficient. Read that article and look at the performance differences he discusses. He talks about a number of ways of splitting strings and other than parsing xml that type of splitter is the worst performer of them all.

Check's in the mail... 😉

WOOHOO!!! That is at least three this month already. I love making money as a promoter of other people's hard work. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

ryvlad (11/8/2011)

I run it against some of my data and it works consistently faster than the one I have.

not by much, about 13 to 16 milliseconds faster, but still faster 🙂

For how many rows? And how did you measure? When you post things about performance, its always best to post what you're comparing to. Please post your original function.

also I made a little modification to it.

I understand that you made the string parameter 8000 char long for backward compatibility, but I needed a bigger value so I changed it to (MAX)

also the last substring() I removed the hardcoded 8000 parameter and replaced it with DATALENGTH(@pString)

do you think it is a bad idea?

You changed almost everything correctly except 1... the cteTally is still only going to count to a max of 10,000. You need to add another level of cross joins and change the source of the ROW_NUMBER().

The MAX datatypes really don't like being joined to. As soon as you change VARCHAR(8000) to VARCHAR(MAX), you've just doubled the amount of time the splitter will take to run even if the things you split stay within 8000 characters. That means that you should probably have two splitter functions... one for VARCHAR(8000) and less and one for VARCHAR(MAX).

Now, for the bad news. You grabbed the wrong splitter code. If you read the Prolog, someone came up with an improvement to the code that doesn't require a "0" based rCTE and it's actually faster than the code in the article. The code you want is actually included as an attachement in the "Resources" section at the very end of the article.

--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".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Sean Lange SSC Guru Points: 286562 More actions · Answer 7

Jeff Moden (11/8/2011)

Please post your original function.

He posted his function at the top of page 2.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ryvlad SSC Enthusiast Points: 162 More actions · Answer 8

Jeff I did not perform any scientific research on this 🙂

I just took your code compiled it on the server

and run it side by side with what I already have.

just to check things out.

it is by no means comprehensive or even in dept test.

just a quick show down. But even so your code outperformed what I already had hands down.

I will look at the alternative code as you suggested,( if you want to use someone else's code you might as well use the latest one

:hehe: )

PS>> you are right about (MAX) datatype.

come to think of it, if you have a row greater then 8000 to be split up you might want to think of alternative way of processing that data anyway.