if a decimal point exists. But if one does not the CharIndex comes back 0 and my equation does not work correctly.
I need to pull the data from the table, along with many other fields. How do I do this without using a cursor? I have millions of rows so need a solution that will be quick.
problem is that the data I am trying to insert is of the type nvarchar. eg: 06.50 I need it to be converted to type decimal (or float) before it is inserted in the new table.
Is there a way to do this within the SELECT TOP expression?
problem is that the data I am trying to insert is of the type nvarchar. eg: '06.50' I need it to be converted to type decimal (or float) before it is inserted in the new table.
Is there a way to do this within the SELECT TOP expression?
I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?
Example: AutomobileTypeId (PK, decimal(10,0), not null)
I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?
I would like to cast (convert) data type decimal(24,4) to decimal(21,4). I could not do this using standard casting function CAST(@variable as decimal(21,4)) or CONVERT(decimal(21,4),@variable) because of the following error: "Arithmetic overflow error converting numeric to data type numeric." Is that because of possible loss of the value?
I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
CREATE TABLE [dbo].[Import_CustomerSales]( Â [CustomerId] [nvarchar](50) NULL, Â [CustomeName] [nvarchar](50) NULL, Â [CustomerSales] [nvarchar](50) NULL ) ON [PRIMARY]
"pRecordSet" is an ADO recordset. The database column "MyColumn" is of type "decimal(19,10)".
The most important question for me is, if the regional settings of the database server or the regional settings of the client PC are considered during the conversion from the string to the decimal value. For example in standard French regional settings the "." would not be recognized as decimal separator.
I am also wondering if the language of the database instance, in which this data is saved, is considered during this conversion or any other settings of this database instance.
So my general question is: Does anybody know exactly what rules apply during the above mentioned conversion?
I have created a simple package that uses a sql command to pull data from an oracle database and inserts the data into a sql 2005 table. Some of the data fields that i am pulling from contain two digits after the decimal point, however this data is lost when it gets into sql. I have even tried putting the data into a flat file, and still the data is lost.
In the package I have a ole db source connection which is the oracle database and when i do the preview i see all the data I need. I am very confused and tried a number of things to get the data into sql, but none work. Any ideas would be very helpful.
I want to strip off the last three characters from an item number. The only thing is tht every item number is not the same lenght. The last three characters of this number are packing codes that I do not need. Fore example I can have all these numbers:
Does anyone know how to get rid of rtf tags that are stored in the table? I need to filter out the data and wondering if there is a utility on the SQL Server that can do it.
Has anyone had to deal with removing punctuation (commas, apostrophes,etc.) from a column? What is the most efficient way to have thesecharacters automatically removed from the column?
Hi everyone, I have a sql quey that selectes phoneNumbers from the database. Problem is some phone numbers have hypens in it and some doesn't. Is there any way in sql so that I can remove hyphens from the phone numbers
some numbers are like this 213-456-9999 and some are 2136789999
I am trying to Strip off the Numbers witha Delimited List and just retain the Name of the Persons. but unable to do it. is there any function or code to do that in SQL
932908` James Fleser,935992` Prakash Sinha
Stripping off Numbers for the Above and Just retain the Names..
Question - if you had to completely strip all permissions from all databases in an instance and reset them, assuming you have metadata to support rebuilding the permissions, what steps would you follow? I can handle the iterating through each database, but at the database level, what steps would you take?
The reason I inquire on this is I currently have a job that I inherited that does just this. But it's buggy and was also written in for SQL Server 2000. With some of the changes in 2005, a few bugs have crept in, etc. And I would like to confirm my thoughts this. Or, if your opinion is why are you wasting your time on this? Then that's fine to and I'll review any constructive comments you may have.
I'm having trouble coming up with a function that will allow me to display only the date and time from a string in the following format:
JSMITH 1/1/2006 1:00:00AM
I've tried using substr with a negative position value, but since the date and time for each instance can be different, with it being anywhere from 18-21 characters, if the length is anything less than 20-21 characters, it will return part of the end of the username. What function can I use to only retrieve the date and time?
Hi everybody,I'm using SQL Server Management Studio Express.I'm trying to create a field which contains a text entered by the user. So, it should be able to contain at least 500 characters.I used the type "nvarchar(MAX)". The problem is that the type contains about 50 characters max!!I couldn't find out where and how to fix that. If you have any idea :)Thanks a lot
I'm trying to strip out the First & Last Name from an Email Address. The email address is formatted as "FirstName.LastName@emailaddress.com" but sometimes a middle initial is used (ex: "First.M.Last@emailaddress.com").
I can locate the '@' sign and the first '.' period(from left to right) but need to find the first '.' from the left of the '@' sign.
I'm doing this in a view.
Here's some sample data and what I've tried:
CREATE TABLE dbo.Table1 (EmailAddr VARCHAR(255)) INSERT INTO dbo.Table1 (EmailAddr) SELECT 'Andy.Smith@gmaddr.com' UNION ALL SELECT 'Betty.Lee-Jones@gmaddr.com'
Value under the column name where id=1 is:-Â uhfhdajkfahgfiasfhgafahsfiahfiahfsai......(upto 600000 characters)
The value under the name column is more than 600000 characters. When i am using the following logic it is storing upto 72000 characters, not reading the whole value under the 'name' column.
Code:- declare @s1 nvarchar(max) declare @substr nvarchar(max) set @s1 = (select name from sampletext where id=1)declare @i int declare @i int DECLARE @j int
[Code] .....
Is there a way to get whole characters under a column instead of storing limited characters?
I wanted to convert a dataset from vb.net (2.0) to an .XLS file, by MS Jet. My national standard is using decimal commas, not decimal points for numbers signing the beginning of decimal places. But the MS Jet Engine uses decimal point,in default. Therefore, in the Excel file only string formatted cells can welcome this data, not number formatted. How can I solve or get around this problem? (with jet if it possible) iviczl
As part of a data search project I need to be able to strip all non numeric characters from a text field. The field contains various forms of phone number in various formats. In order to search on it I am going to remove all non numeric characters from the input criteria and from the data being searched.
In order to do this I decided on using a SQL Server custom function: Pass in field. Loop through all chars, test against asci values for number range. return only numernic data concatenated into a string.
Are there any other more efficient ways of going about this?
Hello, I have this Access 2K query that I need to re-create in MS SQLServer 2000, so I'm using the Query Analyzer to test it.One of the Access fields stores the home phone number. In the Accessquery, if the phone number is null, it fills it up with zeroes"000000000." If the phone has an input mask, it only gets the 9 numbers(area code included) and if the phone number's good (all numbers) thenit leaves it alone. That Access query is using immediate ifs toaccomplish that task.Does anyone have any idea how to copy this behavior into SQL Server2000? I've using the CASE statement but so far my code is not correct.I get stuck in the input mask. This is the Access code:HomePhone:IIf(IsNull([HomePhone]),"0000000000",IIf(Left([HomePhone],1)="(",Right(Left([Homephone],4),3)& Right(Left([Homephone],9),3) & Right([HomePhone],4),[HomePhone]))Thanks for all your help.JR.
I have decimal(6,2) defined in a column. If I insert 90.6, it will display 90.60 , 0 becomes .00 . Is there a way or configuration change so the 0's will on the last will not be showing. And the 0 will display as 0 not .00?
nvarchar(max) I have a nvarchar(MAX) “Description” field in my SQL Server 2005 database. Somewhere I think I read that nvarchar(MAX) is the size of 4000. My question is in my Insert Stored Proc set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[syl_CodeScheduleTypeInsert] @ScheduleName nvarchar(50), @ScheduleDescription nvarchar(????????)ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRY INSERT I What number should replace the ???????? with? nvarchar(4000) is that correct? I tried nvarchar(MAX) and nvarchar but both versions only input one character into my database table. Please help a newbie out, cheers!
Hi i keep getting this error when i search based on Team name (dropdownlist) or coach name(textbox). However it works when i make the search based on the region id(dropdownlist) here is my stored procedure;ALTER PROCEDURE [dbo].[stream_FindTeam] -- Add the parameters for the stored procedure here @coachName varchar(100), @TeamName varchar(100),@regionID INT
AS SELECT TeamID, coachName FROM Teams WHERE coachName LIKE @coachName OR TeamName= @TeamNameOR regionID = @regionID; Here is my code;SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString); conn.Open();SqlCommand command = new SqlCommand("stream_FindTeam", conn); command.CommandType = CommandType.StoredProcedure;command.Parameters.AddWithValue("@coachName", coachName.Text); command.Parameters.AddWithValue("@TeamName", TeamList.SelectedValue);command.Parameters.AddWithValue("@regionID", Region.SelectedValue);SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); DataList1.DataSource = reader; DataList1.DataBind(); conn.Close();
I recently upsized my acess 2000 db to SQL 2000, but I am now having errors . . .
Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E07) [Microsoft][ODBC SQL Server Driver][SQL Server]The sum or average aggregate operation cannot take a nvarchar data type as an argument. /data_count.asp, line 4
The code within the asp file: <% dataSQL = "SELECT SUM(filesize) FROM DL where show=1" Set datacount = my_Conn.Execute(dataSQL) anBytes = datacount(0) %>
I am working on a survey system with another DB Engineer.Our current design includes 1 table per Question Type (Numeric, Boolean, Text), Response values are tightly coupled based on data being entered, to keep things simple assume there are only 6 tables in this this system (1 for each Question Type to define the "rules" of the data entered, and 1 for each Response collection).He is a former .Net programmer and has been talking with the .Net guys on the project and he is proposing that we abstract our Question Types to use one table and store the Input Values all as nvarchar(max).This obviously simplifies the DB design and reduces the amount of work I will need to do so I should be all for it. I guess I am a little concerned that we are no longer tightly coupling the data types and essentially leaving all the validation up to the application. Also should I be worried that a bunch of bit data will be stored as 1/0 in an nvarchar(max) column?Thanks,-John
