Converting varchar to int

Question

Converting varchar to int

Viewing 15 posts - 16 through 30 (of 35 total)

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

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 1

chandrashekar,

It would be much better if you talked to the owners of the original table and got them to normalize that terrible CSV column. If you can't do that, you do NOT need a function call nor a While Loop to build a temporary normalized table for performance purposes. Please see the following article for all of that...

http://www.sqlservercentral.com/articles/T-SQL/62867/

--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)

Saurabh Dwivedy SSCommitted Points: 1887 More actions · Answer 2

For people wishing to learn SET BASED T SQL Programming - I recommend this post whole heartedly. Every single word needs to be understood at the utmost microscopic detail. Many thanks to you Jeff for teaching people the why's and the wherefore's of SET BASED Programming. Thanks to your lucid style and an amazing grasp of the concept not just at the theoretical level but also at the practical level where most beginners like me get stuck - most beginners like myself would find this post so so so very helpful.

Your specific reiterations such as (Notice the start and end commas) and the again that 'notice that multiple selects are getting executed as opposed to a single select' make learning so much easier, direct and fun!.

I cannot thank you enough...

Regards

Saurabh.

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!

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

Wow! Thanks for the feedback, Saurabh!

--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)

Nabha SSCrazy Eights Points: 8534 More actions · Answer 4

Jeff Moden (11/20/2009)
Nabha (11/20/2009)
MY bad! go with Vikas, that is the right solution for you.
Heh... no it's not...that function has a While Loop in it and it's going to be slow. If there's any scalability involved on any of this, not a good idea to use that function.

Tally rules!:w00t: Thanks Jeff. 🙂

---------------------------------------------------------------------------------

chandrashekar.2512 SSC Eights! Points: 965 More actions · Answer 5

Thanks jefff,

This article is very help full to me and thought me how to handle the performance during situation where we need to loop through each record, which is having records like my sample database.

Thanks a lot

Old McDonald SSCertifiable Points: 5768 More actions · Answer 6

Nabha (11/23/2009)
Jeff Moden (11/20/2009)
Nabha (11/20/2009)
MY bad! go with Vikas, that is the right solution for you.
Heh... no it's not...that function has a While Loop in it and it's going to be slow. If there's any scalability involved on any of this, not a good idea to use that function.
Tally rules!:w00t: Thanks Jeff. 🙂

I too agree....Tally rules...Set based programming rules 😀

Thanks Jeff

-Vikas Bindra

lbabichenko 80114 Grasshopper Points: 21 More actions · Answer 7

Simple way to do this without function

Create table #t(t varchar(10))

INSERT INTO #t VALUES ('1,2,3')

INSERT INTO #t VALUES ('3,4,5,11')

select * from #t

where t like '%1%' and t not like '%11%'

Nabha SSCrazy Eights Points: 8534 More actions · Answer 8

Simple way to do this without function
Create table #t(t varchar(10))
INSERT INTO #t VALUES ('1,2,3')
INSERT INTO #t VALUES ('3,4,5,11')
select * from #t
where t like '%1%' and t not like '%11%'

🙂

INSERT INTO #t VALUES ('3,4,5,12')

select * from #t

where t like '%1%' and t not like '%11%'

---------------------------------------------------------------------------------

chandrashekar.2512 SSC Eights! Points: 965 More actions · Answer 9

Hi Jeff,

ALTER FUNCTION [dbo].[fnSplit](

@InputString varchar(8000)

,@Delimiter varchar(10)

)

RETURNS @TempTable table(

ID int identity(1,1)

,[VALUES] varchar(8000)

)

AS

BEGIN

DECLARE @length int

,@Index int

,@LastIndex int

,@counter numeric

SET @InputString = @InputString + @Delimiter

SET @length = len(@InputString)

SET @index = 1

SET @counter = 1

WHILE (@counter < @length)

BEGIN

IF charindex(@Delimiter, @InputString, @index) > 0

BEGIN

SET @lastIndex = charindex(@Delimiter, @InputString, @index) - @index

INSERT INTO @TempTable ([Values])

SELECT substring(@InputString, @index, @lastIndex)

SET @index = charindex(@Delimiter, @InputString, @index) + len(@Delimiter)

END

SET @counter = @counter + 1

END

RETURN

END

How do i use tally table for the above function, as this is taking some time to execute the queries, and it will affect the performance of the application.

Please let me now how do i can dot it with tally table

Thanks & regards

Chandrashekar

Pemmasani Srinivasa Rao. Grasshopper Points: 18 More actions · Answer 10

I think the following is the better code to split the string seperated by comma.

Declare @inputString varchar(1000)

set @inputString='1,2,3,5,6,7,8'

while(charindex(',',@inputString)>0)

begin

print substring(@inputString,1,charindex(',',@inputString,1)-1)

set @inputString=stuff(@inputString,1,charindex(',',@inputString,1),'')

end

nigel. SSChampion Points: 11636 More actions · Answer 11

lbabichenko 80114 (11/23/2009)
Simple way to do this without function
Create table #t(t varchar(10))
INSERT INTO #t VALUES ('1,2,3')
INSERT INTO #t VALUES ('3,4,5,11')
select * from #t
where t like '%1%' and t not like '%11%'

Or maybe this: :w00t:

select * from #t

where t like '%,1,%' or t like '1,%' or t like '%,1'

(DELETE FROM cheek WHERE bodypart = 'tongue') 😀

Nigel
How to post questions

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 12

Pemmasani Srinivasa Rao. (12/3/2009)
I think the following is the better code to split the string seperated by comma.
Declare @inputString varchar(1000)
set @inputString='1,2,3,5,6,7,8'
while(charindex(',',@inputString)>0)
begin
print substring(@inputString,1,charindex(',',@inputString,1)-1)
set @inputString=stuff(@inputString,1,charindex(',',@inputString,1),'')
end

Why do you think a loop is better and better than what?

--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)

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 13

vikas bindra (11/23/2009)
Nabha (11/23/2009)
Jeff Moden (11/20/2009)
Nabha (11/20/2009)
MY bad! go with Vikas, that is the right solution for you.
Heh... no it's not...that function has a While Loop in it and it's going to be slow. If there's any scalability involved on any of this, not a good idea to use that function.
Tally rules!:w00t: Thanks Jeff. 🙂
I too agree....Tally rules...Set based programming rules 😀
Thanks Jeff

Thank you both for the feedback. I really appreciate it... but the real problem is the denormalized table and unless Chandrashekar can convince folks that it's the wrong way to do it, we're going to be stuck with a function one way or another. Even using a high speed Table Valued Function that uses a Tally table to do the split is going to be slower than doing it correctly.

Nigel posted a nice easy way to do this but it's still going to mean that no index will be used correctly like when the table is properly normalized.

--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)

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 14

chandrashekar.2512 (12/1/2009)
Hi Jeff,
ALTER FUNCTION [dbo].[fnSplit](
@InputString varchar(8000)
,@Delimiter varchar(10)
)
RETURNS @TempTable table(
ID int identity(1,1)
,[VALUES] varchar(8000)
)
AS
BEGIN
DECLARE @length int
,@Index int
,@LastIndex int
,@counter numeric
SET @InputString = @InputString + @Delimiter
SET @length = len(@InputString)
SET @index = 1
SET @counter = 1
WHILE (@counter < @length)
BEGIN
IF charindex(@Delimiter, @InputString, @index) > 0
BEGIN
SET @lastIndex = charindex(@Delimiter, @InputString, @index) - @index
INSERT INTO @TempTable ([Values])
SELECT substring(@InputString, @index, @lastIndex)
SET @index = charindex(@Delimiter, @InputString, @index) + len(@Delimiter)
END
SET @counter = @counter + 1
END
RETURN
END
How do i use tally table for the above function, as this is taking some time to execute the queries, and it will affect the performance of the application.
Please let me now how do i can dot it with tally table
Thanks & regards
Chandrashekar

Chandrashekar,

I'll see if I can show you how to use a Tally driven TVF in 2k5 to do this when I take a break for lunch.

--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)

chandrashekar.2512 SSC Eights! Points: 965 More actions · Answer 15

Thanks jeff..

mean while i will also try my side, as the website performance is decreasing i need to tune my sql queries. please help me in this.

Thanks & Regards

Chandrashekar