I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
We capture the firstname of a person along with their middle intial in our contacts table . How do i use the substring function to get just the firstname and omit the middle initial
Sample Data
Jennifer K Jason R Samuel V
I want just the firstname without the space and middle initial
Hi! Can anyone tell me how to perform a substring operation on a DateTime field. I'm using SQLServer. I have read that you may have to use casts but I have thus far been unable to get that to work. My statement: SELECT EventDate FROM tblEvent WHERE Category = 'GE'; I just want to cut the hours, minutes and seconds off, so that I am left with just the date element. Thanks all. Gren
According to the docs, when using substring on a text field (not varchar):
substring (<text>, start, length)
...the length in this case, represents BYTEs as opposed to number of characters. So my question is, how many bytes per character (or characters per byte)....or is this a possible conversion?
I have a Function where I want to put a character in a particular position in a Destination string. For example, I have a 5 character field containing, say, "12345". I would like to put the letter "A" in the third position resulting in "12A45". I'm trying the following
DECLARE @RetVal as varchar(5) SET SUBSTRING(@RetVal,3,1) = 'A'
I'm getting a compiler error
"incorrect syntax near '@RetVal'. Expecting '(' or SELECT"
Am I using the wrong Function to do this? If so, what should I be using?
I am testing something in Visual Basic that talks to a database and I want to filter results by -> field1 like "###". However, that 'like' and '#' is VB syntax. How do you say that in SQL?
I have a part number field which contains whole numbers. (1 - 5000+) however there are gaps in the numbers e.g. Part Number 3876 maybe missing. The table has new parts inserted from a VB Client. Im trying to write a stored procedure where when a user trys to run an update without entering a part number the first missing part number is found. If they do provide a part number then their number is committed as long as it is unique. I've tried using cursors but am not sure on the best approach. Any suggestions?
I am having a problem retaining the right 2 decimal places in my datagrid field. I have it defined as a decimal but when the update happens it rounds the decimal places back to .00. Dim NewMarkup As String 'retrieve the new values NewMarkup = CType(e.Item.FindControl("Markup"), TextBox).Text 'create the ado.net objects Dim command As New SqlCommand("UpdatePricing", connection) command.CommandType = CommandType.StoredProcedure 'parameters command.Parameters.Add("@Markup", SqlDbType.Decimal, 5) command.Parameters("@Markup").Value = CDec(NewMarkup) Thanks!
I need to formate one of my phone number field in SQL. now the data in the field is: 1234567890. I would like to formate the number to 123-456-7890. Does anyone know how to do this? Thanks.
I am developing a message board using ASP on IIS and SQL Server 2000, and I am running into a problem.
When a user enters their post, they enter their user name, subject, and of course all of their post content. Now the post content should be an unlimited number of characters since it could end up being multiple paragraphs.
I know I could just use an ASP file system object and write the text to a file, but I wanted to save all of these paragraphs as a field in a database table so I could pull it from the database so it could be edited by the user.
Is there a way to make a field be able to take in an unlimited amount of characters?
Or is this the wrong way to do things; is it bad to have an unlimited amount of characters in a table field? Should I stick to using the ASP System File Object?
I have a table with over a million rows and one of the fields containsamounts of money in text format.What is the most efficient way of converting this field to a numberformat that I can sum on?Regards,Ciarán
How would I write a query on a table containing a column of ints, where I want to retrieve the rows where that int value starts with a number? I know that you can do this with strings by using "....WHERE thisfield LIKE ('123%')", but if 'thisfield' is an int, how would I do this? Thanks!
Hi, I need to represent two different (number) fields in the same textbox , one number format is C0 (Currency) and other one is Percentage(P1), how can i keep this number format applicable to appropriate fields?
for exaple this is the expression to represent the two fiels in one textbox.
How can I allow users to input numbers with commas into a database field with an 'int' datatype without getting this error, 'Input string was not in a correct format'?
Hi there! I'm using a Switch statement in my SQL as follows:
Code:
SELECT symbol, Switch(timestamp Is Null,Null, timestamp <= 005959, 0, timestamp<=235959,23) AS period INTO averageprice FROM stocktrades;
Now here's my problem. The Data Type stored in the 'period' field of this new table I've created, dubbed averageprice, is a Text field and I want it to be a Number field. I've tried my best to figure this out and I'm still looking so any helpful hints or solution would be greatly appreciated. Thanks!!!
MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?
here how to renumbering stickyId values when deleted particular stickyId from UI. Here stickyId field is Integer type only. not primarykey/identity field.
MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?
Any easier way to do what I am trying, without having a table with all the dates and week numbers.
Scenario: Week number of a DateTime Field where the year does not start on January 01<sup>st</sup> but April 1<sup>st</sup> to 31 Mach.
Issue: A week always starts on a Monday so if the 1<sup>st</sup> is on Tuesday, the first week is Tuesday – Sunday, if the 1<sup>st</sup> April is on Friday, the 1<sup>st</sup> week is Friday – Sunday and 7 day periods from there.
CREATE TABLE [dbo].[DailyCanx](     [ID] [int] IDENTITY(1,1) NOT NULL,     [DateCancelled] [datetime] NULL ) ON [PRIMARY]
[code]....
I could create a table with all the start date and end dates of all the week numbers but I think there must be a better way. using SQL Server 2008 R2.
aaaaa     bbbbb      date       result -------    -------     --------   --------- a           100         7-7-15 a           100         7-7-15 a           100        8-7-15 b           200         7-7-15 b           200         8-7-15 b           200          8-7-15
I need this (divide bbbbb by row count as result, group by "aaaaa and date":
aaaaa     bbbbb      date       result -------    -------     --------   --------- a           100         7-7-15      50 a           100         7-7-15      50 a           100        8-7-15     100 b           200         7-7-15     200 b           200         8-7-15      100 b           200          8-7-15      100
Can I do it?
My SQL Code is
(date=FEC, RESULT=COSTECUB, aaaaaa=TRACTA, bbbbb=TRAPR3)  : SELECT VTRA00F.TRACTA, ACRDES, TRAPRL, TRAPR1, TRAPR2, TRAPR3, CASE WHEN TRAPR3 IS NULL THEN (CASE WHEN (ALLQDE+ALLQCN+ALLQCR+ALLQMR+ALLQNN+ALLQNR+ALLQV1+ALLQV2) <= TRAPRL THEN (TRAPR1*(ALLQDE+ALLQCN+ALLQCR+ALLQMR+ALLQNN+ALLQNR+ALLQV1+ALLQV2)) ELSE (TRAPR2*
Can any one help me, i'm building a dynamic database driven site using dreamweaver and MS SQL2000 andi'm haveing problem storing over 8000 characters in a table filed (IE: it wont let me!!) is there a special table field value that i need to set to get more characters in a table field or is this a limitation of SQL.
If I pull a value from a MSSQL field with is defined as money, how can I get it to display in a textbox with commas and NO decimals? 87000.0000 = 87,000 I can currently remove the decimals like below but is there a way to add the commas as well? decRevenue = drMyData("Revenue") txtRevenue.Text = decRevenue.ToString("f0") It current shows "87000".
Ok I have upgraded my works database from a poorly designed Access database to a SQL database. The previous system allowed NULL values and duplicates to be inserted into a field that should NOT ALLOW NULL Values or duplicates. Therefore, this issue has now been moved across to my new system as I cannot set these constraints on the field that has multiple NULL values.
My solution would be to use a sequential operator, so whatever = NULL would be changed to a sequential number that us as administrators would know was a bogus number starting at something like = 999999900 counting up from that. There are only 250 records that would require updating.
To make things more interesting this field is not a integer type, its a Nvarchar type as its a Hardware ID. Both numerical and characters are require.
When querying a bit field, I am encountering a problem with MS SQLServer returning a larger number of records for a table than theactual number of records that exist within that table.For example, my customer table has 1 million unique records, so theresults of the following query are as such:select count(customer_nbr) from customer = 1,000,000There is bit field in the customer table that denotes whether acustomer has placed an order with us called. That flag is calledorder_flagIf I run the following query:select count(customer_nbr) from customer where order_flag = 1The result is 3,000,000 records.There is no logical way that this is possible, as my table onlycontains 1,000,000 unique records and the number of customers with anorder should be a subset of this.If a run the above query with a distinct before customer number, I getthe results I want:select count(distinct customer_nbr) from customer where order_flag = 1600,000 records.So while I can get to the answer I want, I have no idea why I amreturning incorrect values if I don't select distinct.Can anyone help? I checked microsoft support and message boards buthaven't seen anything.I should note that the bit field is indexed.I am not sure if that isthe problem or not.
I get this error when I look at the state of my SQLresults object. Have I coded something wrong?Item = In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user. conn.Open() Dim strSql As String
