SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select only Numeric Vaues in Field- SQL Sever 2008
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

paramu
Posting Yak Master

151 Posts

Posted - 10/15/2012 :  08:40:22  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/15/2012 :  08:44:10  Show Profile  Reply with Quote
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?
Go to Top of Page

paramu
Posting Yak Master

151 Posts

Posted - 10/15/2012 :  08:56:16  Show Profile  Reply with Quote
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
Go to Top of Page

lionofdezert
Aged Yak Warrior

Pakistan
885 Posts

Posted - 10/15/2012 :  08:58:54  Show Profile  Visit lionofdezert's Homepage  Send lionofdezert a Yahoo! Message  Reply with Quote
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/
Go to Top of Page

lionofdezert
Aged Yak Warrior

Pakistan
885 Posts

Posted - 10/15/2012 :  08:59:13  Show Profile  Visit lionofdezert's Homepage  Send lionofdezert a Yahoo! Message  Reply with Quote
http://connectsql.blogspot.com/2011/05/sql-server-how-to-remove-special.html

--------------------------
http://connectsql.blogspot.com/
Go to Top of Page

paramu
Posting Yak Master

151 Posts

Posted - 10/15/2012 :  09:19:09  Show Profile  Reply with Quote
Thanks...

Paramu @ PARANTHAMAN
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000