SELECT ONLY LAST 5 ZEROS - SQL Server Forums

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)

SELECT ONLY LAST 5 ZEROS

Author

Topic

skybvi
Posting Yak Master

193 Posts

Posted - 2011-10-10 : 11:13:21

hI,
I have a column in a table where the values are all digits (4-14 numbers)
THe values are :-

2003
87378
3859345634098
8000048503485
6929142300000
000004343

From these i just want to select the values in which the last 5 digits are all 0

In the above values last 3 values have 5 zeros but i want only that in whcih the last 5 digits are zeros irrelavent of otehr digits.

For ex. in the above values i want 6929142300000 ONLY.

How to get those values.

Regards,
Sushant
DBA
West Indies

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-10 : 11:18:07

Your example data shows leading zeroes so I assume the datatype is varchar or so.

where right(YourNumbersColumn,5) = '00000'

No, you're never too old to Yak'n'Roll if you're too young to die.

skybvi
Posting Yak Master

193 Posts

Posted - 2011-10-10 : 11:26:22

datatype is nvarchar(14).

Regards,
Sushant
DBA
West Indies

skybvi
Posting Yak Master

193 Posts

Posted - 2011-10-10 : 11:54:33

@WEBFRED

The sql you provided doesn't work :(
I am getting values like '1660000000'
I want just the values where last 5 digits are zeros.

Regards,
Sushant
DBA
West Indies

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-10 : 12:00:56

where fld like '%[1-9]00000'
or maybe
where fld like '%[^0]00000'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

skybvi
Posting Yak Master

193 Posts

Posted - 2011-10-10 : 12:18:36

It worked.

Thanks nigelrivett
and webfred for your help.

Regards,
Sushant
DBA
West Indies

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-10-10 : 17:30:08

the values are all numbers yet the datatype is NVARCHAR?

For why?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-11 : 04:11:40

quote:
Originally posted by Transact Charlie

the values are all numbers yet the datatype is NVARCHAR?

For why?

Charlie

So that leading zeroes don't get lost maybe?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-10-11 : 20:15:11

but why would they be NVARCHAR?

absolutely no requirement for extended characters

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Sachin.Nand

2937 Posts

Posted - 2011-10-12 : 02:35:23

Extension of webfred's method


..where right(YourNumbersColumn,5) = '00000' and right(YourNumbersColumn,6) != '000000'

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources