Opinion About Design Needed (splitting String Data)
Dec 14, 2006
Hi to everyone,
My problem is, that I'm not so quite sure, which way should I go.
The user is inputing by second part application a long string (let's
say 128 characters), which are separated by semiclon.
Example:
A20;BU;AC40;MA50;E;E;IC;GREEN
Now: each from this position, is already defined in any other table, as
a separate record. These are the keys lets say. It means, a have some
properities for A20, BU, aso.
Because this long inputed string, is a property of device (whih also
has a lot of different properities) I could do two different ways of
storing data:
1. By writing, in SP, just encapsulate each of the position separated
by semicolon, and write into a different table with index of device,
and the position in long stirng nearly in this way:
Major device data table
ID AnyData1 AnyData2 ... AnyData3
123 MZD12 XX77 .... any comment text
124 MZD13 XY55 ... any other comment
String data Table
fk_deviceId position value
123 1 A20
123 2 BU
123 3 AC40
.....
123 8 GREEN
The device table, contains also a pointer (position), which might
change, to "hglight" specified position.
Then, I can very easly find all necessary data. The problem is, I need
to move the device record data (from other table) very often into other
history table (by each update). That will mean, that I also need to
move all these records from 1 -8 for example to a separate history
table, holding the index for a history device dataset. This is a little
inconvinience in this, and in my opinion, it will use to much storage
data, and by programming, I need always to shift this properities into
history table, whith indexes to a history table of other properities.
2. Table will be build nearly in this way:
Major device data table
ID AnyData1 AnyData2 ... AnyData3 stringProperty pointer
123 MZD12 XX77 .... any comment text A20;BU;AC40;MA50;E;E;IC;GREEN 3
124 MZD13 XY55 ... any other comment A20;BU;AC40;MA50;E;E;IC;GREEN 2
By writng into device table, there will be just a additional field for
this string, and I will have a function, which according to specified
pointer, will get me the string part on the fly, while I need it.
This will not require the other table, and will reduce the amout of
data, not a lot ... but always.
This solution, has a inconvinance, that it will be not so fast doing a
search over the part of this strings, while there will be no real index
on this.
If I woould like to search all devices, by which the curent pointer
value is equal GREEN, then I need to use function for getting the
value, and this one will be not indexed, means, by a lot amount of
data, might be slow.
I would like to know Your opinion about booth solutions.
Also, if you might point me the other problems with any of this
solution, I might not have noticed.
I have 2 tables, Customers and Organizations. 1 Customer can be under many organizations. What would be the best way to design the db (2 choices) for performance (around 50000 customers): 1) -Customer table -Organization table -Link Organization & Customer by creating a new tables with the following structure ---> CustomerID, OrgID,
2) -A Customer tables that has a field called OrgID where the orgID is stored. If the customer has more organization related to him, we add another customer record and we put the new OrgID in the OrgID column. Examples:
Customer Table -------------- CustomerID--Fname--Lname--Addresss-----OrgID --------------------------------------------- 1 Bob Marley 33 Africa org1 1 Bob Marley 33 Africa org2 1 Bob Marley 33 Africa org3
I have a large poorly designed table (inherited) With a Name field that contains comma delimited text containing address information. I need to do several things with it but unfortunately there doesn't appear to be any true consistency in it. When it displays in its own text box it works by placing each section on a new Line and looks ok.But I need to pull it apart and place things like unit number, Building Name in its own column etc. In the data it could be in either the 2nd,3rd, 4th, dependent on what came 1st. the data looks some thing like the following
unitNumber/StreetNumber Space StreetName (Building Name), Subub,City,Country
Some addresses won't have unit number or Suburb or country so when splitting you could have Suburbs and Citys in multiple columns even if you try and stagger the split process.Has any body go a good tool or reference site for dealing for this sort of problem. I have a table that I have made up that has some of the street names that could be used for comparing against existing records but it is by no means fool proof due to spelling inconsistencies . I also have another list of Common building names that could be used to compare, remove and place in the new building column.
Hi all, I am having problem with SQL connection at Godaddy where my pool connection gets MAX OUT. When it happens, I cannot access the database.This is the thread about the problem: http://forums.asp.net/thread/1665023.aspx I just created this with "THREAD". I hope someone who has experiences with thread can give me some advice about my design. This is my first time.
static object Locked = new object(); public object ExecuteCommand(string queryStr, string type){ //*************************************************************************************// // ExecuteCommand: Returns an object // //*************************************************************************************// Thread t = null; lock(Locked) { SQLString = queryStr; switch(type) { case "ExecuteNonQuery": t = new Thread(ExecuteNonQuery); break; case "ExecuteScalar": t = new Thread(ExecuteScalar); break; case "GetDataReader": t = new Thread(GetDataReader); break; } t.Start(); t.Join(); } return null;} First of, does this work at all? It runs, but is it a good design in term of Thread? Since I use LOCK, do I still need the t.Join() function? The switch with the three cases, is that OKAY? Basically, I'm clueless. If you read my other post, you will get an idea what I'm trying to do. Any feedback would be very very appreciated. Thank you.
I need some other opinions on whether or not this is considered a proper database design structure.
Here is the relationship...We have PEOPLE, that each can belong to a COMPANY.
PERSON_TABLE Person_ID Company_ID
COMPANY_TABLE Company_ID
Then each person can trust other people of other companies, but can only trust 1 person per company.
My question is this. In order to maintain a constraint of 1 person per company, is it considered OK to add a the redundant column Company_ID to the PERSON_TRUSTED_TABLE(and then creating a composite primary key on the Person_ID/Company_ID) instead of just adding a trigger to the PERSON_TRUSTED_TABLE to uphold the constraint.
I have about a 35000 record table. There are about 14 entries in this table that relate to "sections". Each of these sections can have up to 20 values. This lends itself to a design like:
BuildingConstructionType relates to ConstructionHasTypes relates to Types
Where BuildingConstructionType is one of 14 fields in the 35000 record "big" table, ConstructionHasTypes is the one-to-one intermediary relation that relates many-to-many with Types (the sections).
Unfortunately, with 35000 records, this big_table_has_sections seems like it might bloat. Is this a good solution?
I'm a non-programmer and an SQL newbie. I'm trying to create a printer usage report using LogParser and SQL database. I managed to export data from the print server's event log into a table in an SQL2005 database.
There are 3 main columns in the table (PrintJob) - Server (the print server name), TimeWritten (timestamp of each print job), String (eventlog message containing all the info I need). My problem is I need to split the String column which is a varchar(255) delimited by | (pipe). Example:
2|Microsoft Word - ราย�ารรับ.doc|Sukanlaya|HMb1_SD_LJ2420|IP_192.10.1.53|82720|1
The first value is the job number, which I don't need. The second value is the printed document name. The third value is the owner of the printed document. The fourth value is the printer name. The fifth value is the printer port, which I don't need. The sixth value is the size in bytes of the printed document, which I don't need. The seventh value is the number of page(s) printed.
How I can copy data in this table (PrintJob) into another table (PrinterUsage) and split the String column into 4 columns (Document, Owner, Printer, Pages) along with the Server and TimeWritten columns in the destination table?
In Excel, I would use combination of FIND(text_to_be_found, within_text, start_num) and MID(text, start_num, num_char). But CHARINDEX() in T-SQL only starts from the beginning of the string, right? I've been looking at some of the user-defind-function's and I can't find anything like Excel's FIND().
Or if anyone can think of a better "native" way to do this in T-SQL, I've be very grateful for the help or suggestion.
Hi, I'm trying to do insert some data to the database through typing the a number in a textbox. But i will get this error message whenever i tried putting a number that will be inserted to the database. Sorry that the codes are quite messy. Please do let me know which part of the coding contains the errors. Please help me. Thanks. Server Error in '/WSD Project - Cam-Mart' Application.
String or binary data would be truncated.The statement has been terminated. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated.The statement has been terminated.Source Error:
Line 26: Line 27: Protected Sub add_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles add.Click Line 28: SqlDataSource2.Insert() Line 29: Response.Redirect("Checkout.aspx") Line 30: Source File: C:Documents and SettingsAdministratorDesktopWSD Project - Cam-MartOrder.aspx.vb Line: 28 Stack Trace:
Protected Sub add_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles add.Click SqlDataSource2.Insert() Response.Redirect("Checkout.aspx") End Sub Protected Sub Back_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Back.Click Response.Redirect("Catalogs.aspx") End Sub Protected Sub checkout_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles checkout.Click Response.Redirect("Checkout.aspx") End Sub End Class
Hello,I have been placed in charge of migrating an old access based databaseover to sql server 7.0. So far, I have imported all the tables intosql server, but now I have come across the issue of needing to split astring variable. For instance, in the old database, the variable forname was such that it included both first and last names, whereas inthe new database there are seperate entities for first and last name.I know that there is a way to write a script that will separate out thetwo strings by using the "space" in between the name, but I'munfamiliar how to do this. Any suggestions? Thanks!Rick
I'm trying to split a hyphen-delimited string into three columns in a view. I've been using substring and len to split up the string, but it is getting very complicated (and isn't working in all cases). I've used a SPLIT function in vbscript - does t-sql have anything similar? I've attached a spreadsheet that shows what I am looking for. Maybe someone can guide me in the right direction?
I need to creat distinct terms of the example parsing the term on the '|' character. I will be using mysql.
example: 1885-1974.|Johnson family|Frontier and pioneer life - Alberta - Black Hill district|Cadogan region (Alta.) - Biography|Black Hill district (Alta.) - Biography
I have a column in a table which looks like below.
Column ------- AA123 D123 AXC1 QF23
I need to split this value into two part, Alphabets and numeric. How to do this using SQL query.My column value will not have mixed characters like A1D3,G32S,12F.It will always follow the ablve pattern mentioned above.
I'm creating a web-based NT RAS report site and am looking for the most efficient way to import the data from NT Event log into SQL2k. I'm using the 'dumpel' utility from rsc kit and all is fine except the 10th column - the message detail:
"The user DOMAINuserid connected on port Mdm15 on 08/23/2002 at 07:25am and disconnected on 08/23/2002 at 07:27am. The user was active for 2 minutes 23 seconds. 78809 bytes were sent and 50675 bytes were received. The port speed was 49300."
I need to parse this one long text string into 6 distinct columns: userID, port, duration, bytes_xmt, bytes_rcv and portspeed. After a quick review of the rowsets, the strings seem to hold a consistent output ... no real variances I can see.
I've dablled with views but am facing a small performance issue that could get bigger: The sql server not only has to run the text file import package, but also the view to format the text dump into a workable dataset, then my report code bangs over 30 queries against the final dataset. It already takes our SQL2k server over 3 minutes to parse about 20,000 rows and the server's a beast (dual 1.8 p4 cpu, 3gb ram, raid, etc).
What I think would work best is to abandon the view (performance will only get worse as the row count increases) and instead INSERT the rows into one table.
Any ideas anyone? any good scripts out there that can help me to parse the long text string quicker that using substring and replace functions?
So we have a field called forenames, and it needs to be split into fields forename_1, forename_2, forename_3, forename_4 (don't ask).
Ok, I've come up with this so far, which works, but is pretty nacky in my opinion. Has any one got a better way of achieving this?
SELECT forenames , Replace(forenames, ' ', '.') , Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 1)) As [f1] , Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 2)) As [f2] , Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 3)) As [f3] , Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 4)) As [f4] FROM ( SELECT 'John' As [forenames] UNION SELECT 'John Paul' UNION SELECT 'John Paul George' UNION SELECT 'John Paul George Ringo' ) As [x]
Results
forenames (no column name) f1 f2 f3 f4 ---------------------- ---------------------- ---- ---- ------ ----- John John John NULLNULL NULL John Paul John.Paul John PaulNULL NULL John Paul George John.Paul.George John PaulGeorgeNULL John Paul George Ringo John.Paul.George.Ringo John PaulGeorgeRingo
I have a column named "LIST" in a table with strings like the following:151231-1002-02-1001151231-1001-02-1001151231-1002-02-1002151231-1003-02-1001etc....What I'd like to do is include an ORDER BY statement that splits thestring, so that the order would be by the second set of four numbers(i.e. between the first and second - marks), followed by the third setof two numbers, and then by the last set of four numbers.How would I do something like this?--Sugapablo - Join Bytes!http://www.sugapablo.com | ICQ: 902845
I need a help in SQL Server 2000. I am having a string variable in the format like -- (1,23,445,5,12) I need to take single value at a time (like 1 for 1st, 23 for 2nd and so on) from the variable and update the database accordingly. This is like a FOR loop. Can anyone help me out in splitting the variable using the comma separator...
Hi All!!! I was tasked to come up with a search function and the content of the database given to me is in Chinese Characters. This would be my first time dealing with Chinese characters in the database and I need help with the following problem: The company wants to conduct the search in such a way that, instead of having the system read the entire sentence/phrase which the user keyed in as a SINGLE string, they want the Chinese Characters to be accessed individually, so that as long as any information in the database contains any one of the characters which the user have entered, they will be retrieved and returned. So how do I go about doing this? Does it have anything to do with Unicode? By the way, everything abt the search tool is working fine, I am just left with this dilemma of having the system recognise the entire sentence as ONE STRING, instead of conducting a search word by word or character by character. Anyway, the following is the SQL statement of my SQL Data Source which is bound to a Gridview displaying the returned results after a search is done...1 SELECT Name, Trans, Address1, Address1T, Address2, Address2T, City, CityT, CRPLID 2 FROM CRPL 3 WHERE (Trans LIKE '%' + @Trans + '%') OR 4 (Name LIKE '%' + @Name + '%') OR 5 (Address1 LIKE '%' + @Address1 + '%') OR 6 (Address1T LIKE '%' + @Address1T + '%') OR 7 (Address2 LIKE '%' + @Address2 + '%') OR 8 (Address2T LIKE '%' + @Address2T + '%') OR 9 (City LIKE '%' + @City + '%') OR 10 (CityT LIKE '%' + @CityT + '%')
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
This works very well, other than instances of the delimter are, themselves, considered to be results. For example:
SELECT * FROM vs_SplitTags(' ', 'foo bar') AS result returns: pn s 1 foo 2 bar
which is exactly the result I would want.
However, SELECT * FROM vs_SplitTags(' ', ' foo bar ') AS result -- There are spaces before 'foo' and after 'bar' returns pn s 1 2 foo 3 bar 4
And SELECT * FROM vs_SplitTags(' ', 'foo bar') AS result -- There are two spaces between 'foo' and 'bar' returns pn s 1 foo 2 3 4 bar
I want the function to ignore whitespace altogether, be it a single space or multiple spaces. Other than to delimit the boundries between words, of course.
In other words, all three examples above should produce the same result: pn s 1 foo 2 bar
How can I do this? Any thoughts much appreciated...
Hi, hoping someone can give me a heads up on best sysyem to employ to manage this scenario.Sorry if this is not the correct forum to post in.Our primary software is a standalone VB6 app used by some 50 clients, each with thier own MSDE backend. Their business basically is Packing and distribution of fresh produce. Each of our clients have multiple suppliers on which they have data stored. I have decided to write a web app (VS2005) that will allow the supplier to enter critical information about produce quality, testing etc. This data will be kept on our local webserver database.My use case scenario is that when a supplier enters a new record via web page I can somehow update the Packers SQL database via internet, http,tcp.Ideally I imagine a trigger on our server db can connect to their databaseover WAN. HOW??The packer must also be able to add suppliers to our server db. As they already have that data stored stored at their end I was hoping to have a web page with drop down lists that accesses their local db and, upon Save, updates our webserver ddb. Thereby saving them the need to manually enter the data twice. I know this is a little long winded but I'm new to .Net and web programming so have no idea on the simplest and best method to do this.Any help greatly appreciated.
Hi Friends, I need to use UML for designing the database in my application. I am using sqlserver2005 database. can somebody help me in this regard? thanks in advance.
Hi there everyone, I am starting to develop a new internal website for my company, for logging calls, reports and billings. I am still busy looking at the best way to design the layout of the DB. My biggest concern is a billings table design. What it should do is, that when work was completed, the consultant must add his billings to the logged call. My problem is that i will never know how many individual items will be billed for. I was thinking of just creating a table with [id, call_id, item_1, cost_1, quan_1, item_2, cost_2, quan_2, item_3, cost_3, quan_3...........] but i think (hope) that there must be a better way of doing this. RegardsJacques Thomas
Hello, I was wondering if any of you experts might assist me in properly creating a database for my application. I've been pondering for a few days on how to accomplish this, but it seems that it doesnt have to be as complex as I am thinking...I just have to know how.
First off, I want to populate a dataset from a database in which I can databind to a datalist or repeater control. My datalist or repeater will have the following information in the ItemTemplate. Each ItemTemplate will consist of a table with 1 row with 3 columns cells. In each cell, the data will be laid out as accordingly.
When the datalist or repeater is populated, it will need to be tried against a value that the user selects. For instance, the user selects 100,000 from a listbox and fetches the next page which will show the diagram above...he/she will see those results. However, if the user selects 110,000, the diagram above will have for the most part the same Unique Number and Descriptions, but the prices will vary. The reason I say for the most part with the Unique Number and Descriptions, is because I want to later be able to add admin access to add additional rows and appropriate prices to each Unique Number and Description.
Now the tricky part is, I could just add the listbox values to a table as a unique key and associated them with the Unique Number, Description and Price, however there are about 50 different options the user can select from and approx 50 different rows of Unique Numbers, Descriptions and Price . So you can see, I would have to set up the diagram each time for every possible selection from the listbox, which wouldnt really be efficient I presume.
I want to be able to populate the datalist or repeater so it could have say 50 Unique Numbers with Descriptions and Price at selection 100,000. And also that it might only populate with 45 Unique Numbers and Descrptions and Price at selection 110,000 because the 5 missing dont pertain to the selection of 110,000. I am trying to do it this way because at sometime, I want the admin to be able to add/delete a Unique Number, a Description for it and a corresponding price that correlates to the selection from the listbox.
Thanks for all your help guys. I really appreciate it. For the most part I understand what Im doing, I just need to be walked thru it a bit. Thanks again!
I am helping a friend with a gamming website. There are pages which displays data from other clans.
Members need to register and login to view full clan details. User who have not logged in can only view partial data about a clan.
I need to keep track of the kind of hits each page received. That is I want to tell say "Clan X" that these particular members viewed your page these many times and on these dates and these members who have not registered have viewed your page these many times and on these dates.
I am using ASP with MS SQL.
I would like some help on designing the table layout that is efficient for keeping track of the visitors for each page.
This query was working well because I used to only be interested in one counter that was returned in the column, which was 'Free Megabytes'...I now have additional data that shows up as 'Total Disk Space'...Ideally, the query would return the total disk space next to the free megabytes on the same row for the same disk drive. Here is a couple rows of sample output:
AverageValueInstanceNameObjectNameCounterName 44549 C: LogicalDiskFree Megabytes 44548 C: LogicalDiskFree Megabytes 69452 C: LogicalDiskTotal Disk Space 69452 C: LogicalDiskTotal Disk Space
This is the ideal format, the average value column goes away:
InstanceNameObjectNameFree MegabytesTotal Disk SpaceC: LogicalDisk44549 69452
Hello and thanks in advance to any and all help on this post!
I am trying to create a report that uses a simple select against one table in the database:
select a,b,c from MyTable where d = 1 order by a
For ease of explanation, this returns 75 records. This report is to be used as a one page Flyer. Now I can create a single table and format it but I end up with three pages instead of one. My thought was to split the data returned between two side by side tables on this report. I cannot seem to find a way to do this through the properties of each table or an example of any expression that could help with this outside of RowCount (which simply does a page break), nested SELECTs to emulate LIMIT as in MySQL, or SELECT TOP n ORDER BY ASC / DESC to get a TOP N or BOTTOM N from a SQL Query.
I know I can't be the only one to have ever thought of this as a solution, I hope not at least , so I was hoping someone here may be able to help out. Thanks again in advance!
