| Author |
Topic |
|
zgall1
Starting Member
9 Posts |
 Posted - 2012-05-01 : 17:25:57
|
| I have a field in a table that is structured as follows:XXXXXXXXXXXXXXXYYYYZZZZZZZZZZThe part labeled with Zs is always 10 characters long. The part labeled with Ys can be anywhere from 4 to 6 characters long. I am trying to separate out those two parts of the string so I can use them as part of a join with another table where they are two distinct fields. Can anyone suggest how I can dynamically determine where the X part of the string ends and the Y part begins? Thanks. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-05-01 : 18:03:39
|
Based on your example, this would work, but I suspect you are not telling the whole story here. Is it really repeated characters?DECLARE @x VARCHAR(255);SET @x = 'XXXXXXXXXXXXXXXYYYYZZZZZZZZZZ';SELECT RIGHT(@x,10),REPLACE(REPLACE(@x,'X',''),'Z','') |
 |
|
|
zgall1
Starting Member
9 Posts |
Posted - 2012-05-01 : 18:07:37
|
| Sorry. I should have been more clear. The characters described as Ys are a string of numbers. The characters described as Xs are a string of numbers and letters. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-05-01 : 18:12:06
|
| If X's can be numbers and letters and Ys are all numbers and Y's can be anywhere from four to six characters long, I don't think there is a reliable way to predict whether the Y's are four characters long or six characters long. For example, if the string was 1111111111111111111ZZZZZZZZZZ, how do you determine where the Y's begin?If you post realistic examples, I am sure some of the people on the forum would be able to offer possible solutions. |
|
|
|
zgall1
Starting Member
9 Posts |
Posted - 2012-05-01 : 18:27:08
|
| Here are some samples from the database. I have attached explanatory notes to each item. CTCX567916384INV0007778 (line number 16384 for invoice INV0007778)IPPlus131072INV0007778 (line number 131072 for invoice INV0007778)IPPlus8192RTN0000123 (line number 8192 for return RTN0000123)Nextel32768RTN0000123 (line number 32678 for return RTN0000123) |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-05-01 : 18:32:06
|
The first row in the example highlights the problem I was describing in my previous post. Since line number can be four to six digits long, it could be 6384, 16384 or 916384. How did you determine that it is 16384 and not either of the other two possibilities?CTCX567916384INV0007778 (line number 16384 for invoice INV0007778) |
|
|
|
zgall1
Starting Member
9 Posts |
Posted - 2012-05-01 : 19:28:32
|
| I understand the complexity of the problem fully and I'm not sure there is an easy way around it.In this case, I know that the line number starts at 16384 only because that is the convention that this application uses for the first row of any invoice. The second row is typically 16384 * 2 = 32678. It can unfortunately get quite complicated when a row is deleted (for instance, a row that is deleted and reinserted as the first row will be number as 16384/2 = 8192). |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-05-02 : 06:50:09
|
If you cannot express a formula/logic to be used to determine how many digits to pick up, I don't see a way in which a query can either. Assuming you can come up with the logic, you can use the following - simply replace the red 4 with whatever is the rule for determining how many characters to pick up.DECLARE @x VARCHAR(255) = 'CTCX567916384INV0007778';SELECT RIGHT(@x,10), RIGHT(STUFF(@x,LEN(@x)-9,10,''),4); |
|
|
|
|
Struggling with a join