how to convert string to numeric in sql server 2008

Posted by Apremalatha under Sql Server on 8/22/2013 | Points: 10 | Views : 1241 | Status : [Member] | Replies : 5
convert string to numeric in sql server 2008,in this string does not have numeric value




Responses

Posted by: Satyapriyanayak on: 8/22/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111618
http://sqlserverplanet.com/tsql/convert-text-string-to-numbers-int
http://blog.sqlauthority.com/2007/07/07/sql-server-convert-text-to-numbers-integer-cast-and-convert/

If this post helps you mark it as answer
Thanks

Apremalatha, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/22/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
hi
you can query as follows
[code]select cast(columnName as dec(8,2))
from tablename
where isnumeric(columnname) =1[/code]

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Apremalatha, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/22/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
in the where condition you can check for numeric values
[code]where columnname not like '%[a-z]%' [/code]

condition: the above code will only work in the case of alphabets, but it will not handle special characters

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Apremalatha, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/22/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
i'm sorry i can't able to edit the above post using mobile....

If you want to convert to integer only use INT istead of DEC(8,2)

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Apremalatha, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 8/23/2013 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi

try this code


CREATE TABLE [dbo].[t11](
[prodid] [int] NULL,
[Invno] [varchar](50) NULL,
[InvAmt] [numeric](18, 2) NULL
) ON [PRIMARY]


Select CAST(prodid AS varchar) FROM t11

Mark as Answer if its helpful to you

Apremalatha, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response