| Author |
 Topic  |
|
|
paramu
Posting Yak Master
141 Posts |
 Posted - 10/15/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/15/2012 : 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
141 Posts |
Posted - 10/15/2012 : 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
Pakistan
864 Posts |
Posted - 10/15/2012 : 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
Pakistan
864 Posts |
|
|
paramu
Posting Yak Master
141 Posts |
Posted - 10/15/2012 : 09:19:09
|
Thanks...
Paramu @ PARANTHAMAN |
|
|
| |
Topic |
|
|
|
Select only Numeric Vaues in Field- SQL Sever 2008
