Select only Numeric Vaues in Field- SQL Sever 2008

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

General SQL Server Forums

New to SQL Server Programming

Select only Numeric Vaues in Field- SQL Sever 2008

Author

Topic

paramu
Posting Yak Master

151 Posts

Posted - 2012-10-15 : 08:40:22

Hi
Iam using SQL Server 2008. I wish to select only numeric values in a item_code field. Is it possible?

Item_Table
==========
item_code, item_name, item_description from Item_Table
itm0, Pencil, Stationeries
itm1, Pen, Stationeries
itm2, Eraser, Stationeries
itm3, Table, Assets
itm4, Chair, Assets
and so...on...

My Query
========
select (item_code)????? as my_code_number, item_name, item_description from Item_Table

Thanks

Paramu @ PARANTHAMAN

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-15 : 08:44:10

Do you mean that
a) you want to select only those rows in which item_code is a number - e.g., you have 1234 and abc24 in two rows, and you want to get only one row out of those two - the one that has 1234

or
b) your item_code is a combination of digits and other characters, and you want to pick out the numbers out of those? e.g., you have 1234 and abc24 in two rows and you want to get two rows with item_codes as 1234 and 24?

paramu
Posting Yak Master

151 Posts

Posted - 2012-10-15 : 08:56:16

Hi sunita, thanks, after long gap, again I get you today... I wish to get the result like the below

0, Pencil, Stationeries
1, Pen, Stationeries
2, Eraser, Stationeries
3, Table, Assets
4, Chair, Assets

I wish to select only the numbers in item_code as new field...

Thanks

Paramu @ PARANTHAMAN

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-10-15 : 08:58:54

for such removals i like to use following functions

alter FUNCTION dbo.RemoveSpecialChars
(
@InputString VARCHAR(8000)
)
RETURNS VARCHAR(8000)
BEGIN
IF @InputString IS NULL
RETURN NULL
DECLARE @OutputString VARCHAR(8000)
SET @OutputString = ''
DECLARE @l INT
SET @l = LEN(@InputString)
DECLARE @p INT
SET @p = 1
WHILE @p <= @l
BEGIN
DECLARE @c INT
SET @c = ASCII(SUBSTRING(@InputString, @p, 1))
IF @c BETWEEN 48 AND 57
--OR @c BETWEEN 65 AND 90 --also remove A to Z
--OR @c BETWEEN 97 AND 122 --also remove a to z
--OR @c = 32
SET @OutputString = @OutputString + CHAR(@c)
SET @p = @p + 1
END
IF LEN(@OutputString) = 0
RETURN NULL
RETURN @OutputString
END

--------------------------
http://connectsql.blogspot.com/

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-10-15 : 08:59:13

http://connectsql.blogspot.com/2011/05/sql-server-how-to-remove-special.html

--------------------------
http://connectsql.blogspot.com/

paramu
Posting Yak Master

151 Posts

Posted - 2012-10-15 : 09:19:09

Thanks...

Paramu @ PARANTHAMAN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources