August 11, 2013 at 5:50 pm
I have an excel sheet being imported with 8 columns. One column (say name)has comma delimited string.
Difference with most other posts.. is that the 1 column that has the delimited string can have any number of values delimited. So some name field could be 12 substrings, some could be just 2.
I need to ensure entire string < 2048 and every substring < 128
Values will be checked for existence in current table.
At the end, I will be displaying the values in the column as a string, but I could probably modify the stored procedure that displays info.
I was thinking
1) Use Tally Table to convert substrings to rows, but that would have a lot of repeated info with 8 columns.
2) Use some other technique to convert substring to columns, but I have to check for the string with max values and then set those many columns with maybe a lot of nulls.
I need some input as to what is the best way to handle this scenario.
Any help is much appreciated.
August 11, 2013 at 11:56 pm
whereisSQL? (8/11/2013)
I have an excel sheet being imported with 8 columns. One column (say name)has comma delimited string.Difference with most other posts.. is that the 1 column that has the delimited string can have any number of values delimited. So some name field could be 12 substrings, some could be just 2.
I need to ensure entire string < 2048 and every substring < 128
Values will be checked for existence in current table.
At the end, I will be displaying the values in the column as a string, but I could probably modify the stored procedure that displays info.
I was thinking
1) Use Tally Table to convert substrings to rows, but that would have a lot of repeated info with 8 columns.
2) Use some other technique to convert substring to columns, but I have to check for the string with max values and then set those many columns with maybe a lot of nulls.
I need some input as to what is the best way to handle this scenario.
Any help is much appreciated.
From what you are saying, the only reason for you having to break the string up is so that you can verify that max(split_field_column_length) < 128 - is that correct? Do you have any requirement to save the split fields separately?
What do you want to happen if the max length is exceeded?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 12, 2013 at 5:55 am
That is correct. There is no requirement to break it up other than to check if max(split_field_column_length)<128.
If max length is exceeded, I want to just read up to max length (discard the other values). Either the 128 or 2048.
It is actually very unlikely that our max is going to be hit, but I guess you never know.. and the requirement specifies the max.
Breaking up the string was.. what I thought of to solve the problem, so it may not be the best solution. I am open to other ways to solve it.
August 12, 2013 at 6:49 am
whereisSQL? (8/12/2013)
That is correct. There is no requirement to break it up other than to check if max(split_field_column_length)<128.If max length is exceeded, I want to just read up to max length (discard the other values). Either the 128 or 2048.
It is actually very unlikely that our max is going to be hit, but I guess you never know.. and the requirement specifies the max.
Breaking up the string was.. what I thought of to solve the problem, so it may not be the best solution. I am open to other ways to solve it.
OK - and I imagine that you would apply the 2,048 limit after first trimming the individual fields, if necessary?
I can't think of any better way of solving this than breaking up the column.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 12, 2013 at 7:21 am
2048 is before trimming
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply