Parsing A Field Data And Populating Other Fields
Nov 3, 1999
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.
Thanks for your help!
Scott
View 1 Replies
ADVERTISEMENT
Sep 5, 2014
I have a text field that I need to parse out into 4 fields.
30.125x23.625-18.875x25.375
6.1875X19.375-2.4375x15.625
0X0-0x0
26.875X11.375-11.125x22.875
0X0-0x0
0X0-0x0
6.1875X26.875-2.4375x23.125
6.1875X26.875-2.4375x23.125
6.1875X26.875-2.4375x23.125
26.625X14.875-11.125x22.875
26.625X14.875-11.125x22.875
I need to 26.625X14.875-11.125x22.875 should be 26.625 then 14.875 then 11.125 then 22.875
View 5 Replies
View Related
Apr 11, 2008
in my project,i have the 6 record types,in the flat file ,
i have to load the data into the loadaccount table like ( evrey record type has certain fields and i load those fields in to the different accounts.)
could anybody help regarding this issue?
View 2 Replies
View Related
Sep 24, 2005
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
View 21 Replies
View Related
Sep 28, 2005
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
View 3 Replies
View Related
Jun 9, 2015
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 ?
View 9 Replies
View Related
Jan 15, 2015
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.
OLD FIELDNEW FIELD
B123F
B123H
B123T
OLD FIELDNEW FIELD
B123F
B123H
B123T
View 2 Replies
View Related
Oct 1, 2007
Can I make a calculated field by using two fields from different data sets?(I'm talking about SSRS data sets)
I tried to do that. But I got a error message.
"Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope."
Please can some one help me out?
View 5 Replies
View Related
Jan 9, 2008
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);
View 5 Replies
View Related
Jul 6, 2007
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.
View 2 Replies
View Related
Aug 18, 2000
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.
Any ideas or help will be appreciated!
View 1 Replies
View Related
Aug 9, 2012
I have a name field that has many variations and been fighting with it for a few days. The possible name variations are:
Smith, Mike
Smith, Mike K.
Smith, Mike K., JR.
I would like to parse each of the for pieces in a separate field.
View 3 Replies
View Related
May 13, 2007
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...===================
View 3 Replies
View Related
Oct 6, 2006
I am trying to parse a single field into many. A sample of the data in one of my fields is:
0;127.20;3;23.90;13.56;0;0;0;0
I would like to parse it into 9 fields. I am sure that this is easy, but, since I am new to SQL2005, I am having a few probs with this simple task.
Any assistance will be greatly appreciated.
Layne
View 5 Replies
View Related
Feb 12, 2015
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.
View 0 Replies
View Related
Oct 11, 2001
Does anyone have a script already written to parse a text field in SQL?
View 1 Replies
View Related
Jul 12, 2007
Hello,
I have a field that has the following data:
<li>Invalid email address.</li><li>Invalid zipcode.</li><li>Password is required.</li><li>
And, I'd like to parse this into rows separated by the "<li>"
Thanks!
View 1 Replies
View Related
Jan 29, 2008
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.
View 1 Replies
View Related
May 9, 2006
Hi,
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?
Thanks.
View 3 Replies
View Related
Sep 25, 2007
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???
View 2 Replies
View Related
Sep 5, 2007
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.
View 1 Replies
View Related
Jul 20, 2005
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?
View 1 Replies
View Related
Oct 3, 2007
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?
View 1 Replies
View Related
Jan 18, 2005
Hi
Need to populate data from SQL Server in a pdf file which is basically a government form.
Data should be fetched from the SQL server database and needs to be displayed in a pdf file.
Advice me on how to implement this.
Suggest me if there is any other idea for implementing the same.
Thanks in advance
View 1 Replies
View Related
Jul 13, 2007
I have a project that entails the following:
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.
Thanks in advance.
View 9 Replies
View Related
Oct 31, 2007
HI,
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?
Thanks
View 2 Replies
View Related
Nov 16, 2004
Hi all,
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?
Thanks
Tryst
View 2 Replies
View Related
May 21, 2015
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;
Date Number WeekNo
18-5 25 21
19-5 25 21
20-5 25 21
21-5 25 21
22-5 25 21
23-5 30 21
24-5 30 21
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;
Date Number WeekNo
25-5 30 22
26-5 30 22
27-5 30 22
28-5 30 22
29-5 30 22
30-5 30 22
31-5 30 22
Because the last result (ordered by date) of the week number 22 is 30, the whole week shows 30 on every day.
View 2 Replies
View Related
Oct 11, 2007
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.
Here is the code as it stands now:
//Populate the DataGridView
string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Environment.CurrentDirectory + @"DB.accdb;Jet OLEDBatabase Password=MyPassword;";
// 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;
Any help and information is greatly appreciated.
Thanks Again,
View 5 Replies
View Related
Jan 28, 2008
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.
Thanks,
Sirisha
View 3 Replies
View Related
Mar 6, 2015
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
View 6 Replies
View Related
Aug 24, 2006
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?
What is 2005's answer to this?
View 4 Replies
View Related
Apr 21, 2000
Do anyone know of any functions I can use to parse the following data eg.
M 3480-7 should be 3480
M 3477-19 should be 3477
M 28-10 should be 28
Thanks in advance,
Vic
View 1 Replies
View Related
