How to remove last 2 digits from an integer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to remove last 2 digits from an integer

Author

Topic

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-08-10 : 01:43:01

In sqlserver stored procedure, if i send an number(int) as an input i want to check that number is > 3 or not. If number < 3 then use that number in query otherwise if number > 3 remove only last 2 digits of a number.

i.e If number is 234 then use it directly.
If number is 23467 i.e > 3 then consider only 234
Note: dont use Left function. Because may be the lenght of number be 1 or 2 also.

How can write this in store prodecure

If (@Num > 5)
@NUM = ..

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-10 : 01:52:16

declare @i int

select @i =123

select len(@i),CASE WHEN len(@i) > 3 then substring(cast(@i as varchar(32)) ,1,3) else @i end

select len(@i),CASE WHEN len(@i) > 3 then LEFT(cast(@i as varchar(32)),3) else @i end

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-08-10 : 02:02:33

Hi thanks for your reply. But instead of considering first 3 letters. please tell me the methog to remove last letters in a number. The reason why i am asking is programatically i will get the number from user as "85260-1111" or "5260-1111" . In programming i removed the hypen and store it in DB as 852601111, 52601111
So , i want to remove last 4 digits then i will get output as 85260,5260.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-10 : 02:06:28

select substring(cast(@i as varchar(32)),1,len(@i) - 4)

or select LEFT(cast(@i as varchar(32)),len(@i) - 4)

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-08-10 : 02:23:56

Thank you

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-08-10 : 02:34:59

I am getting covnersion problem here. Can u look in to this once

declare @Zipcode int
Declare @ZcodeToUse INT

select @Zipcode =852601111

SET @ZcodeToUse = select Convert(INT,LEFT(cast(@Zipcode as varchar(32)),len(@Zipcode) - 4))

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-10 : 02:37:02

quote:
Originally posted by Mng

I am getting covnersion problem here. Can u look in to this once

declare @Zipcode int
Declare @ZcodeToUse INT

select @Zipcode =852601111

SET @ZcodeToUse = select Convert(INT,LEFT(cast(@Zipcode as varchar(32)),len(@Zipcode) - 4))

Do like this

declare @Zipcode int
Declare @ZcodeToUse INT

select @Zipcode =852601111

select @ZcodeToUse=Convert(INT,LEFT(cast(@Zipcode as varchar(32)),len(@Zipcode) - 4))

select @ZcodeToUse

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-10 : 02:41:39

quote:
Originally posted by Mng

I am getting covnersion problem here. Can u look in to this once

declare @Zipcode int
Declare @ZcodeToUse INT

select @Zipcode =852601111

SET @ZcodeToUse = select Convert(INT,LEFT(cast(@Zipcode as varchar(32)),len(@Zipcode) - 4))

You can't do explicit convention. If you assign to int variable it get automatically (implicit) became int.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-08-10 : 02:44:10

its work perfect. Thank You dude.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-10 : 02:46:39

welcome :)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-10 : 03:07:42

declare @Zipcode int
Declare @ZcodeToUse INT

select @Zipcode =852601111

select @ZcodeToUse=@Zipcode/10000
select @ZcodeToUse

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-10 : 03:09:31

declare @Zipcode int
Declare @ZcodeToUse INT

select @Zipcode =852601111

select @ZcodeToUse=left(@Zipcode,len(@Zipcode)-4)
select @ZcodeToUse

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-10 : 03:28:20

This?

DECLARE	@Sample TABLE
	(
		Data INT
	)

INSERT	@Sample
SELECT	234 UNION ALL
SELECT	2345 UNION ALL
SELECT	23467

SELECT	Data,
	Data / POWER(10, FLOOR(LOG10(Data)) - 2)
FROM	@Sample

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources