|
I'm probably in the wrong forum,
But I made a huge mistake in a table design for Billing and Shipping addresses in relation to card processing.
I made a single column called attention for the name. But card processors want a first and last name for Billing and Shipping, and a single column for card name.
Opps!
So now I have all these names in a single column.
Name like
Thomas Friedman Sr.
Loopy Jose De SannaAna
Margret Keeneman-Hassel
Kenneth G. Tompson Jr.
So I can do a split in vb.net, and split them up, but I'm not sure how to place them.
Just looking for ideas before I write any code to do the job.
Perhaps just take the first set of chars, and that's the first name, and then the rest is the last name?
|
|
|
|
|
IMHO it is a issue you can not resolve with code...
Human names - and the way they are written down - are reflecting the human brain - lack of any order...
As long as you have only one whitespace in the name you can simply split it, but what should you do with those with more than two parts in it?
There is no existing algorithm to take them apart...(And if you add international customers, you are in deep...)
What I would advise is:
1. Change the design of the tables, when the existing name will go into new 'firstname' column for the beginning...
2. Create logical groups of the names and
2.a. Split the simples
2.b. Check for those with Mr., Msr., Jr. and so in it and split them
2.c. Scan remained names for groups and handle them individually
2.d. Handle manually the rest (hopefully not much)
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
It's good to get a 2nd opinion on that, and you confirmed it.
I did change the design to let the customer choose what is the first and last name, sounds dumb but practical.
So I did a split and select or switch case 2, 3 and 4 - first last, first (1) last, first (1+) (1+), first (1) last (SR-JR)
So out of 5000, I only had 30 Mexican names, that i did by hand.
Thanks for the confirmation!
|
|
|
|
|
You're welcome...
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
i created a db through script and now i want to restore in a new DB on a new server.
i need method.
|
|
|
|
|
Would that be MySQL, or another database-server?
If it is a MySQL-script with create and insert-statements, you'll only need to execute it. If it is a SQL Server backup, you'd need to use the restore command.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
yes it is in Sql Server 2008r2. kindly share the restore commands with complete procedure.
|
|
|
|
|
Restore[^] on MSDN.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I painted myself into a corner here. Was going to do a join, but realized I had nothing to join.
Is it possible to get this result in 1 set?
SELECT
cc.ID
, cc.CustomerID
, cc.SessionID
, cc.LoginID
, cc.CardLabel
, cc.CardBrand
, cc.CardName
, cc.CardNumber
, cc.ExpMonth
, cc.ExpYear
, cc.CVV2
, ba.Name1
, ba.Name2
, ba.CompanyName
, ba.StreetAddress1
, ba.StreetAddress2
, ba.City
, ba.StateCode
, ba.PostalCode
, ba.CountryCode
, ba.Phone
, sa.Name1
, sa.Name2
, sa.CompanyName
, sa.StreetAddress1
, sa.StreetAddress2
, sa.City
, sa.StateCode
, sa.PostalCode
, sa.CountryCode
, sa.Phone
FROM CUSTOMER_CARDDATA cc
WHERE cc.ID = @CC_ID
FROM CUSTOMER_BILLING_ADDRESS ba
WHERE ba.ID = @BA_ID
FROM CUSTOMER_SHIPPING_ADDRESS sa
WHERE sa.ID = @SA_ID
<pre>
|
|
|
|
|
If there is no relationship between the tables then that would not be possible unless you create one or you are getting only 1 record from each table, then you could use a cross join
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The only relationship is the master order table, but it can be different in 3 scenarios
I'll check out the cross join.
Last night I just used one of the master tables, and wrote 2 functions.
|
|
|
|
|
If each of the three resultsets contains exact the same columns, then you can use the UNION keyword.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
If there's exactly one matching record to each other and they're in the same order you could use the ROWID to join them / to update the tables without ID with the ID from CUSTOMER_CARDDATA.
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
Can we install any version of sql server on Windows 8.1?
|
|
|
|
|
Probably not "any", but any recent Express version should do.
Full specs here[^].
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
How to use SQL Server in Windows 8, Windows 8.1, Windows Server 2012, and Windows Server 2012 R2[^]:
- SQL 2005: Not supported.
- SQL 2008: Needs at least SP3.
- SQL 2008 R2:
- Needs at least SP2 on Windows 8.1 / Server 2012 R2;
- Needs at least SP1 on Windows 8 / Server 2012;
- SQL 2012:
- Needs at least SP1 on Windows 8.1 / Server 2012 R2;
- No SP required for Windows 8 / Server 2012;
- SQL 2014: No SP required.;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
We have a software that provide data from automobile (like parts of various vehicle). For now we release / provide DVDs at a regular interval of time to our users so that they can remain updated with required data.
Now we are planning to provide data update via internet i.e. client's DB can be updated based on some information available on internet. This will help us to get rid of releasing monthly DVDs.
Any idea on the process? Please help.
|
|
|
|
|
This is simply a method of delivery, all you need to do is get your user to download the DVD content and initiate the local update. So get your app to check a URL for an update available flag and prompt the user to download and then install using your current method.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You will get better ideas if you reply through the forum! There are others who will be able to help better than I.
Kukreja Sonu wrote: Thanks Holmes. As of now we write all the changed data into an XML file at server and upload the same to a centralized location. Then client downloads the xml and executes queries one by one accordingly to update target DB.
But this is a long process:
1. Populating DB at server with change only data
2. Initiate process to prepare XML based on above data.
3. Client downloads the XML and update target DB.
This takes around 3-4 days for single run. So, we want to have some other process if possible to minimize the time
The more I think about this the nastier it gets, are you up to changing the way you identify your change sets or do you have to stick with the current design/structure?
First I would look into change the format from xml to something less verbose. XML is fine for communication as a transport medium it sucks. We use CSV.
It sounds like each client has a different requirement!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
SELECT
Label
, (SELECT BFirstName, BLastName FROM CARDINFOPERM WHERE CardID = 1121) AS BAttention
, BCity
FROM CardInfoPerm
WHERE CardID = 1121
|
|
|
|
|
Perhaps
(SELECT CONCAT(BFirstName, ' ', BLastName) FROM CARDINFOPERM WHERE CardID = 1121) AS BAttention
This works, not sure if it's safe
(SELECT CONCAT(BFirstName, ' ', BLastName)) AS BAttention
modified 2-Jun-15 16:21pm.
|
|
|
|
|
Since it's the same table you can just write it like this:
SELECT
Label
, CONCAT(BFirstName, ' ', BLastName) AS BAttention
, BCity
FROM CardInfoPerm
WHERE CardID = 1121
jkirkerx wrote: not sure if it's safe
If you're thinking about null-values:
Quote: Null values are implicitly converted to an empty string. If all the arguments are null, an empty string of type varchar(1) is returned.
(From https://msdn.microsoft.com/en-us/library/hh231515.aspx[^])
So you might want to surround the CONCAT(..) with a TRIM(..).
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
Cool
Thanks for the quick help Sascha!
|
|
|
|
|
You're welcome! 
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
I know I should use a stored procedure to do this but ths is not possible. In the code below return values from the "select" statements are being assigned to code variable values being concatenated with the actual Select statement. I am not talking about the code in the Where clause but the code in the "Select" portion. See the string as below:
query.Append(" SELECT e581_key, 'e581', 'rnlt', rnlt, '" + _rnlt + "' , 'SaveEditRNLT', '" + _logUserName + "', getdate() ");
query.Append(" FROM e581 "); <pre>
The select statement is then assigned to the DbCommand.CommandText of the command.ExecuteNonQuery.
How do I move the actual code variables being concatenated to some type of command parameter?
modified 2-Jun-15 15:29pm.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
