| Author |
Topic |
|
JezLisle
Posting Yak Master
132 Posts |
 Posted - 2008-11-13 : 05:56:57
|
| I am making a temp storage table, so data can be used in another application. I the PatientID in my main table is VARCHAR(20) and I need to convert this to fit into my Temp table. In there its a Numeric(18,0) format. How is it posible to do this in my SP where I delete the original data and reinput the revised data? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-13 : 06:00:10
|
| use the convert() function.watch out for decimal separators though._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
 |
|
|
JezLisle
Posting Yak Master
132 Posts |
Posted - 2008-11-13 : 06:18:24
|
| Thanks, how would I do this? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-13 : 06:25:03
|
by writing some code that checks if the decimal separator is correct for your locale  but as i see your target column is decimal (18, 0) you won't be able to store any decimals anyway so you probably don't have to worry about it._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
JezLisle
Posting Yak Master
132 Posts |
Posted - 2008-11-13 : 06:36:13
|
| Ok, I have never done this sort thing before, how would I start to write a procedure that takes a string and then convert to numeric? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-13 : 06:59:14
|
| decalre @yourStringVariable varchar(100)select @yourStringVariable = '123,456,789'-- select @yourStringVariable = replace('123,456,789', ',', '.') -- repalce all , to .select convert(decimal(38,0), @yourStringVariable) as ConvertedToDecimal_______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-11-13 : 07:16:23
|
quote:
Originally posted by spirit1
decalre @yourStringVariable varchar(100)select @yourStringVariable = '123,456,789'-- select @yourStringVariable = replace('123,456,789', ',', '.') -- repalce all , to .select convert(decimal(38,0), @yourStringVariable) as ConvertedToDecimal_____________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!
Why replace('123,456,789', ',', '.') ?? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-11-13 : 07:32:43
|
| declare @yourStringVariable varchar(100)select @yourStringVariable = '123,456,789'select convert(decimal(38,0), cast(@yourStringVariable as money)) as ConvertedToDecimalMadhivananFailing to plan is Planning to fail |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-13 : 07:34:36
|
DECIMAL(18, 0) uses 9 bytes for storage.BIGINT uses only 8 bytes.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
JezLisle
Posting Yak Master
132 Posts |
Posted - 2008-11-14 : 07:06:34
|
| I dont understand this, how do I build a SP that will insert and change this from TableA to TableBThis is the SQL I haveINSERT INTO jez.SWM_FINDNHSNO ([NHSNo], [Surname], [Forename], [Gender], [DateOfBirth])SELECT PATNT_REFNO_NHS_IDENTIFIER, SURNAME, FORENAME, SEXXX_REFNO_DESCRIPTION, DTTM_OF_BIRTHFROM dbo.CD_PATIENTSHow do I or where do I use this below in the code above?DECLARE @NHSNo VARCHAR(20)SELECT @NHSNo = '4787648144' (I dont want to just change one record I need to change thousands)SELECT CONVERT(NUMERIC(18,0), @NHSNo) AS ConvertedToNum |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-14 : 09:57:16
|
SQL Server will convert tyhe value for you, as long as it is possible.If you are sure you have no other characters than digits, you are safe.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
Convert VARCHAR into Numeric