I have just started to look at SQL and have a theory question that I could apply to a test I want to run. I have some legacy data from a previous project and the database was not designed properly (in my opinion). They have ONE field to capture City and State information. All the data is formatted City, State .
Does SQL have commands that can look at data in a field, strip out info before and info after a comma and then write that to other fields?
So, I would like to normalize this to take the data in a field called CityState and parse it, trim it and then populate two new fields 1) City and 2) State.
What are some good strategic approaches to using freeform text fields fordata that needs to be queried? We have a product whose tables we can'tchange, and I need to count on a "description" field for storing a value.Two, actually. I'm thinking of adopting this convention:InvoiceNumber@VendorAcronymThere'd be a lot of vendors.Additional issue: sometimes these values would be referred to in thedescription field, and I'd need to distinguish them as referrals ratherthan as original recorded instances of the values. For that, I imaginedeither:InvoiceNumber@@VendorAcronymorInvoiceNumber&VendorAcronymInvoiceNumber//VendorAcronymetc. -- something like that.I'm just wondering if there's best practice for doing anything this stupid(hey, I'm stuck with this as our only option just now; hopefully it's onlytemporary). How to parse out whatever I end up implementing -- well, itneeds to be tractable.Thoughts?--Scott
Hi,I am running SQLServer2000 with ASP.NET. I have 3 fields:DATE_TIME (date time 8)DATEF ()TIMEF ()...when my default.aspx page is called, i need to execute a stored procedure that will parse my DATE_TIME field by the space in between, then update my DATEF and TIMEF fields with the appropriatevalues. NOw i have 2 fields to query from instead of 1. How would i write a SQL expression to do this? and what datatypes should the DATEF and TIMEF fields be?thxj
I have a table with raw scientific test results in a single field, some of which are over 25Mb field. I need to parse into the field to find and aggregate selected values from the field.
Table structure is CREATE TABLE [dbo].[Gxxx_Data]( [id] [uniqueidentifier] NOT NULL, [Status] [nvarchar](50) NULL, [GxxxItem_ID] [int] NULL, [Stats_Data] [varbinary](max) NULL, ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
[code]...
From which I need to parse and summarize the (Assembler) opcodes (MOV,CMPi, SHR etc...)I need to parse the large field [Stats_Data] to locate the target data.The internal result strings are delimited with Char(10), conservative counts are from 64k to over 100k lines in each record. Is there a way to parse the individual lines into another table (temp) that would be queried/regexed ?
I have a field where all of the data is 5 characters in length. The last character denotes a status and will always be an F, H, or T. I want to add a new field (which I will do manually) and populate the new field with the last character from the "old" field. Once that is complete, I want to eliminate that 5th character from the old field.
Hi, Im trying to pull data from 2 fields in the same table into a SqlDataSource that feeds into a GridView, and display them as 1 field in GridView? I have a database table that has entries of users and their friends. so this tblFriendUser has a column called UserName and another column called FriendUserName. I am trying to get a list of friends for that particular user. Note that if User1 initiated the friend request, he will be listed as UserName and his friend as FriendUserName, but if his friend initiated the friend request, it will be vice versa: him being the FriendUserName and his friend the UserName. So I want the following 2 queries run and merged into one query in order to return 2 columns only: UserFriendID & UserName, is that possible? Is my design bad? Any suggestions/advice would help! Thanks a lot!
SELECT UserFriendID, UserName FROM tblUserFriends WHERE (UserName = @UserName);
SELECT UserFriendID, FriendUserName AS UserName FROM tblUserFriends WHERE (FriendUserName= @UserName);
Can you parse a SQL field? Let's say, FULLNAME field got a TEXT datatype with the following data: <firstname>Norm</firstname><lastname>bercasio</lastname><Color>blue</color>then using a select statement, parse the field to find the lastname then write it to another field called LASTNAME on the same table, same rowID. Can you send a select statement how it can be done? I am using SQL 2003 or 2005. thank you so much.
Anybody out there ever take a column containing names and parse it out to salutation, first name, middle initial/name, last name, suffix using Transact-SQL? I think I know how to do it using an array in a procedural language, but using SQL I'm drawing a blank.
hi..i need help for my exam, I retrived data from my sms inbok andinsert to table.The record just like this:ID1,12x100,56x12,90x90..,how to pars the table in to:ID First SecondID1 12 100ID1 56 12ID1 90 90Thank for your help...===================
I have 2 databases: one for our intranet & another for our internet website.In the intranet database, we have a table called "Clients". A new client can be added in one of 2 ways:
1) One of our employees manually adds the client via the intranet 2) A new customer subscribes to our services via our website, thus becoming a new "client" (when subscribing online, we also add a record to an "Accounts" table located in our internet website database, but we'd also like to add a record to our intranet's Clients table as well).
In the client table is a field called "CreatedBy" which expects the UserId of whoever created the account. Again, this UserId can belong to either an employee via the intranet or a new customer via our internet website. how do I distinguish where the UserId in the dbo.Clients.CreatedBy field is coming from?
My 1st thought was to append a negative to the CreatedBy UserId# if it came from a customer via the website, but that just seemed too quirky (i.e. if an employee added a client and his UserId was '10', the Clients.CreatedBy would be '10'. If a customer subscribed via the website, the Clients.CreatedBy would be '-10'. Where customers can once again sign up online. So, I need a way to keep these databases separate but keep track of which db a user's coming from when we create a new Client record.
I'm trying to parse an ntext field that in my SQL View contains an invoice comment in order to be able to group on parts of the comment. I have two problems--one, the syntax to do this, and two, the best way to deal with the parts that I want.
The comment is like: "standard text ABCDE : $99.99" but can have multiple "ABCDE"s, e.g. "standard text ABCDE FGH IJKL $999.99" and I found some that had duplicates like "standard text standard text...". I want to be able to report in SSRS 2005 by grouping the "ABCDE", "FGH", "IJKL" items.
Any ideas? Please be specific as I'm still learning.
My table has 2 fields as 'count' and 'codes'. The 'codes' field has 'count' # of code values in each record. Size of each code is 4. For example, if my record is 2,'abcdefgh' then there are 2 codes and the values are 'abcd' and 'efgh'.
Currently I am using 'script component' to parse the field into multiple values. Since I have to read 1 million records and on an average, each record has 10 codes, it is taking hrs to load it.
Can it be done without 'script component' using some other transformations?
I'm a bit stumped at this. I have done this before a bunch of times. The difference is that this time I have two rectangles in the footer. For the each, the visibility is set to an expression which checks whether the page is page 1 or 2. If it is 1, the first rectangle is visible, if 2, the second. When I reference the hidden fields on the main area of the report via ReportItems, they come through fine on the first rectangle, but are invisible on the second. I've tried the same exact fields and every time they are visible in rectangle1, and invisible in rectangle2. What is going on here???
I am using two tables, courses and video with a one to many relationship with the videos table being the many. How do I populate the courseID in the video table with the corresponding courseID from the courses table using a combination of Formview & Gridview or two Gridviews, or a dropdown field populated with the course name and courseID in a Form or details view.This is a simple task in Access accomplished using a dropdown list to select the correct video.
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?
Hi All, I have a startdate (01/11/2007) and a enddate (01/11/2008). I need to add dates into a table for everyday between these dates. Can anyone help?
There are two separate SQL Server databases involved that reside on two different servers. One of the depts within our building wants to have building permit data imported from Permit Database on Server "A" to their own database on Server "B".
I dont think this will be an overly complicated process. There are only a few fields they want populated (5 or 6 tops). This will have to be ran every weeknight via some sort of scheduled task in Windows or SQL.
I was just interested in seeing if anyone has had prior experience working on data transfer like this. I would like to know what would be the best and most efficient way to approach this.
I populed my data base. But before I populate I created some reports beased on this DB. Now after I populating I can execute query and see results in Management studio or query designer. But When I change it the report to preview mode I can not see it. Does anyone know why this is?
I have a table where I want to populate it with data (certain fields) from another table. But I only want to populate the table if there is data in a certain field in the table I wish to get the data from.
How exactly would I do this test? Would an I have to perform an IF statement to test if the field contains a value first?
I have a date table(A) for every day of the year from 1990-2016 including the week number.There is another table(B) that has a number, date and week number.
I would like to join these 2 tables together, in order to populate the number from table B on every day of the week (of the week number in table B).
What makes it a little more complex is that the dates in table B not always correspond with the week number in the same row. Adding to the problem are some conditions:
- Every day of the week should have a number, and it should never be 0.
- If the 1st date of all rows in table B with the corresponding week number is not the monday of the week (but for example wednesday) it should start with this number on monday.
- Thousands of rows are in table B and for some of them the date corresponds with the week and for some of them they don't
- The rows can be grouped together using the week number.
Take the following example of table B:
B: ID Date Number WeekNo 1 21-5-2015 25 21 2 23-5-2015 30 21
In this example the dates correspond with the weeknumber, because the 21st and the 23rd of may are week 21. By joining this with the date table (A), by using for example cross apply, I would hope to get the following result;
The same should work if the same example had the week number 22 in every row.
Take the following example of table B:
B: ID Date Number WeekNo 1 21-5-2015 25 22 2 23-5-2015 30 22
In this example the dates do not correspond with the weeknumber, because the 21st and the 23rd of may are week 21 and not 22. By joining this with the date table (A), I would hope to get the following result;
I am sorry for asking such a broad question, but I have been working on this and from what I can gather it can be done. My problem is that much of it has gone right over my head and I am getting more confused the more I read... I'm really, really confused...
Basically, I have a dataGridView that is populated with a number of fields from Table1 (ID, NameID, Status, Phone, Notes). This works fine, BUT I would like to access Table2 and have, where ID in Table2 = NameID in Table1, it load the First Name & Last Name into the dataGridView. I am able to load the information from Table 2 like so: SELECT NameFirst + ' ' + NameLast from Table2", but I can't get both Tables to work correctly.
I would like the dataGridView to be layed out like this:
ID NameID Name (NameFirst + NameLast) Status Phone Notes
I can't for the life of me understand or get this to work (Or for that matter even understand what I am trying to do...
Also, I am using Access 2007.
I would greatly appreciate some help, and possibly some explanation in laymans terms so that I might be able to understand this. I have read a lot about this, but for whatever reason it is just soooooo over my head that I can't follow it whatsoever.
// create and open the connection OleDbConnection conn = new OleDbConnection(conString); OleDbCommand command = new OleDbCommand(); command = conn.CreateCommand();
// create the DataSet DataSet ds = new DataSet();
// run the query command.CommandText = "SELECT ID AS [#], NameID AS [Name], Status AS [Status], Phone AS [Phone], Notes AS [Notes] FROM Table1 WHERE ID = " + textBox13.Text + ";"; OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter = new OleDbDataAdapter(command); adapter.Fill(ds);
// close the connection conn.Close();
bindingSource1.DataSource = ds.Tables[0];
dataGridView1.DataSource = bindingSource1;
// set the size of the dataGridView Columns this.dataGridView1.Columns[0].Width = 10; this.dataGridView1.Columns[1].Width = 100; this.dataGridView1.Columns[2].Width = 100; this.dataGridView1.Columns[3].Width = 100; this.dataGridView1.Columns[4].Width = 176;
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
Table Name: EmployeeDetails Columns: EMpID - Date - WorkedHours
For each day I get details of number of hours worked by each employee in this table.
Now my HR wants a report with such columns
empid - Week - Month - Qtr
So, week will have Sum of hours worked by employee in that week Month will have Sum of hours worked by employee in that Month Qtr will have Sum of hours worked by employee in that Qtr
In 2000, BCP seemed the way to go. DTS packages would also work. My question is, in 2005, what is the best choice? I seem to remember that BCP ignored all referential integrity constraints, and applying them afterwords was a royal pain. I'm not a BCP expert by any means. Running this at the command line means using the DOS prompt correct?
