Building a string range
-
I have a customer number that I'm trying to setup a between initial customer number range and ending customer number range. The problem is that this data has to be text (varchar) because I have both numeric values and text values within a customer number. I have about 12000 records having a multiselect box on the client won't work. How can I setup a varchar range?
Thanks in advanced
-
Are you filling in blanks? Can you give us a set of your data? (This may not require a full description of the tables, expected and actual results - sounds like a string manipulation problem...)
I wasn't born stupid - I had to study.
-
Can you provide a few example customer numbers that demonstrate both numeric and text values and then tells us how you would sort those manually, what logic you would use your self to determine the order if you had to manually sort the customer numbers? Before you can create a query to sort these you must first indentify what logic you would use yourself to sort them manually. Then you determine how to convert that into a SQL statement that performs the same logic that your mind uses. While some could try and guess at this in their reply's to your post it would be better if you would give us an example so we could see how you would do it manually.
Ed
Kindest Regards,
Just say No to Facebook! -
I changed the range method and decided to use the IN function instead this worked out better. As I mentioned before this customer number is a varchar because it contains numeric values say 1-10000 and then assigns alphas such as MSWINDOWS456, etc. I intially thought I could convert this to INT but ran into data types errors and when setting up ranges I didn't have a sequential pattern for the alpha customer numbers. It just becomes a data nightmare. This data is being pulled for an old ERP system that contains no validation. I decided to go the "IN" route.
Thanks in advanced
-
If you end up needing that number from the varchar field, post some data and I am sure someone here will be able to help you strip it out. Either way, the integer design is usually the best choice! Good luck...
I wasn't born stupid - I had to study.
-
Split the varchar and int portions for storage purposes only. You can still combine them for display, etc..
So, create two new columns, one varchar and one (small)int, copy the existing data to them, drop the existing column, recreate the existing column as a computed column that concatenates the varchar and (small)int columns.
The sooner you split this the easier your life will be
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
The issue with this customer number is that in some cases the entire customer number is alpha for example, in some cases the user has entered say MICROSFT as a customernumber. The only consistent data pattern is that it's inconsistent in the way the customernumber is stored. As previously mentioned there is limited validation on the ERP system. The biggest nightmare is the customer data there are no standards.
It gets worse I've asked who is responsible for setting up the rules and I'm still waiting for the answer two years later. It's been a struggle in that regards.
Until I can create standards and more importantly the user community adhere to these rules I'm going to have to work within the current framework.
Thanks,
-
Feel for you buddy! It probably started out as a "home made" system in Access or something.
Take charge. Put the #%^ IDENTITY integer in there and start enforcing the relationships. I think you are clearly on the right track!
You may need an intercept table for the possible many-to-many relationships betweeen your ID field and the current "CustomerNumber". That may be a booger to keep in synch...
Good luck!
I wasn't born stupid - I had to study.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply