Hi All there - I want to show the o/p of a cursor on a single line. There is a numeric variable that needs to be clubed with the character variable. If I use char() the o/p is not right. How do I do that?
HiI have a character field (char ot varchar) that I want to force only tocontain numeric characters.Can that be done by way of defining a constraint on the field ?or by any other way in the field/table definition ?What id the syntax ?Anyone have examples ?ThanksDavid Greenberg
how i convert varchar sal field to numeric in query select sum(sal) from emp1 error:the sum or average aggregate operation cannot take a varchar data type as an argument.
I am getting a date string that is contained in a character field (char(20)).
An example of the data is as follows:
2005-09-20121315001
it is in the format YYYY-MM-DDHHMMSSMMM.
I want to insert this value into a datetime field. I have used convert, but can only insert it when I split out the time portion and separate them with a colon
i.e. to get it to insert into a datetime field the data has to be in the following format
2005-09-20 13:15:18.001
YYYY-MM-DD HH:MM:SS.MMM
Is there a style parameter in the convert function that will allow me to insert this value without having to separate the hours/minutes/seconds with a colon? I have tried BOL but it seems that all the examples have colons in them where there is a time portion to the date,
Hello, I am from the school of thought that you should in every case have your primary keys as numeric values only. However, where I currently work there is a project leader who is a recent FoxPro convert (I know, they are tough ones to crack). I made the suggestion recently that the keys in the table should be numeric and with him being the project leader and me just a lowely developer he said get lost. I made the point that later joining your tables together in a PK/FK relationship where the keys where character would be slower then with numeric keys. He didn't listen and now we are approaching production with a database that is really just a bunch of text file. He said that with SQL 7 it doesn't matter if the pk is numeric or character. I disagree. But I need solid documentation to take to him and to the managers to convince them. If anyone out there could advise me on this. And if anyone could give me or tell me where I could find documentation on why even in SQL 7 there is a need to use numeric keys that would be a great help and you would be making one more shop in this world a little bit more technically sound :-) Thank you in advance for your help.
In SQL I need to be able to take a varchar parameter @Area and convert it to a float.
The input values for @Area I can't control. They can range from 6300 to 6,300 SqFt to 1.2 Acres .
So to convert this value to a float I basically look through the string and remove everything that isn't a number or a period. Then I would convert this value to square feet based on how large the number is.
select @k = patindex('%[^0-9. ]%', @Temp) while @k> 0 begin select @Temp = replace(@Temp, substring(@Temp, @k, 1), '') select @k= patindex('%[^0-9. ]%', @Temp) end
If @Temp = '' BEGIN SET @Temp = '0' END
SELECT @SqFt = Convert(Float, @Temp)
--Distinguish if it was acres or square feet If (@SqFt > 750.00) BEGIN SET @SqFt = @SqFt END ELSE BEGIN SET @SqFt = (@SqFt * Convert(Float,43560) ) END
SELECT @SqFt
This works great except for one situation, If @Area is something like 6,300 Sq.Ft. . When I run it through the part that removes all non-numeric items and periods, I end up with 6300 .. . So to get around this I want to find the first letter in the string and then remove everything after it. Then take the result and run it through part that removes everything but the numbers and period.
However I can't find away to get the index of the alpha-numeric character and remove everything after it.
I need create a field to store tax rate. I need only 2 decimal points. I defined the field as decimal, precision=5 and scale=2. Does it mean that it can hold value from 0.00 to 999.99?
Hi guys/ladies I'm still having some trouble formatting a select statement correctly. I am using a sqldatasource control on an aspx page. It is connecting via odbc string to an Informix database. Here is my select statement cut down to the most basic elements. SELECT commentFROM informix.ipr_stucomWHERE (comment > 70) The column "comment" contains student grades ranging from 0-100 and the letters I, EE, P, F, etc. Therefore the column is of a char type. This is a problem because I cannot run the above statement without hitting an alpha record and getting the following error "Character to numeric conversion error" How can I write this statement where it will work in the datasource control and have it only look at numeric values and skip the alpha values? I have tried case with cast and isnumeric... I don't think that I have the formating correct. I have also used: WHERE (NOT (comment = ' I' OR comment = ' EE' OR comment = ' NG' OR comment = ' WP' OR comment = ' WF' OR comment = ' P' OR comment = ' F')) This works but is very clunky and could possibly break if other letters are input in the future. There has to be a better way.I am sorry for my ignorance and thanks again for your help.
Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen
I know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?
I have imported data from excel file. When data came to SQL table, the typeof AMOUNT column was varchar. I tried to convert and cast amount type ofamount column to number type but it does not allow me to convert.What is the best way of importing data into SQL and type stays the same asit was in excel file ?Or anyone has any better solution, please let me.Thanks.
Hi all, I defined an user string type varible in the package as AccountLen. I am trying to use this varible in the Expression of Derived Column transformation. I want to retrieve a part of column, i.e: Right(Column1, @AccountLen), this is always wrong because the AccountLen is string type. How I can convert it to the numeric so that can be used in the RIGHT function? Thanks
I read the topic from JROdden and this case is similiar but...
I got several varchar fields with values like 1.2 1.3 ... these I can covert with select CONVERT(dec(5,2), fieldname) as fieldname
In fact I also solved undefined- and NULL-values with. CONVERT(decimal(12, 2), CASE WHEN GESCHKOSTMAX IS NULL OR GESCHKOSTMAX < '0' THEN '0' ELSE GESCHKOSTMAX END) as GESCHKOSTMAX,
But now there are values like 1,4 and these ones neither CONVERT nor CAST will handle.
I tried the SELECT DISTINCT KMPAUSCHALE FROM extr_INTFIRMA WHERE (isnumeric(KMPAUSCHALE) = 1)
and get 0,40 0.25 0.30 and so on...
The error is: [Microsoft][ODBC SQL Driver][SQL Server]Error converting datatype varchar to decimal. (or float or numeric (whatever I tried))
I think the easiest way would be to insist on higher data quality but I also would like to solve this interesting challenge.
Thanks for any hints
By the way, I followed rudys link to http://rudy.ca/afdb.html and now I know how I could protect myself !!!!
There must be a voice in my head saying: Try the db-forum, try it and stay happy... ;-)
I have to store the result of a calculation in a column of type CHAR(7) (and am unable to change the column type).
The calculation can have results ranging in size from 0.1234567 to 99999999.
In the first case, I would need to store the value of 0.12345 in the column. In the later case, an error should be thrown.
So I need to store all of the significant digits from the left of the decimal (if there are < 7) and as many of the digits to the right as will fit into a CHAR(7), with the remaining precision being truncated.
I have a problem that I am unable to fix. I am migrating data from one ERP system to another ERP system.
I created a table in SQL via DTS by converting an excel file into an SQL Table.
The table has a column 'currentcost'. Most items in the table have a value in this column. However, some items have <null> in this column. I am updating another SQL table with currentcost that is in this column. However, my table being updated requires a value in its corresponding currentcost table. Therefore, my update statement terminates due to the source table having <null> values.
I tried an update statement to convert <null> into a '0'
It was 'update Source_New_ItemMaster set currentcost = '0' where currentcost = <null>
This obviously didn't work. How can a change a <null> value in an SQL table to '0' (zero)?
I have imported some data to sql2k from my old system. Somehow, it importedinvoice amount to char type.I just created another column called invamt2 type NUMERIC so I can copy orconvert content of invamt which is type CHAR. There are about 50,000records.How can I convert/cast from char type to numeric type ?Thanks
Hi again all,I have a small issue. Here's an example dataset :F1 F2 F31 0.58 Hi2 0.70 Hello3 Fail Bye4 <Null> HiWhen I write this statement :SELECT SUM(CONVERT(DECIMAL(16,8),F2)) MySumFROM T1WHERE IsNumeric(IsNull(F2,'X'))=1I get "Cannot convert a Varchar value to Numeric" error. From what Iunderstand, it somehow tries to convert to a decimal(16,8) BEFORE filteringthe nulls and the non-numeric out. (Keep in mind that the actual table hasover 1.5Million records).Any idea on how to get around that ?Thanks,Michel
Sorry to raise a stupid question but I tried many methods which didwork.how can I conserve the initial zero when I try to convert STR(06) intostring in SQL statment?It always gives me 6 instead of 06.Thanks a lot.
I have column as month and value as January, february. etcc....
can we convert this character column into date&time format.. if we do that what will be output..
I am doing test instance, when i sort by month column it's sorted in alphabetical order. when we convert this into date&time, it's sorts by date&time, it puts me right direction..
I am loading a flat file to a table but I also need to scrub the data a bit before the data hits the table. The main update required is converting a dot (.) character to a null value. The source file is using this character to indicate a blank. I know I can use the Dervived Column Transformation, but I have quite a few columns which will take a while to manually configure. Is there another transformation option that anyone can point me to?
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits. The mfg_start_date is delcared as a string variable
option 1 I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
