RollinNow
asked on
Find trailing comma and space in sql query
When using this line of code I get an intermittant CF error:
keyword= listGetAt(meta_keywords,1)
CF error was:
"In function ListGetAt(list, index [, delimiters]) the value of index, which is 1, is not a valid index for the list given as a the first argument (this list has 0 elements). Valid indexes are in the range 1 through the number of elements in the list."
Meta_keywords is a comma delimited list of keywords. I believe the error is thrown from a trailing comma at the end of the last meta_keyword, sometimes with an extra space, such as:
'one, two, three, '
How can I indentify a trailing comma in the sql 2000 database using an sql query? I'm not sure how to construct such an sql query and remove it.
To find the trailing comma:
SELECT META_KEYWORDS
FROM AREAS
WHERE (META_KEYWORDS LIKE '%,')
To find the trailing space:
SELECT META_KEYWORDS
FROM AREAS
WHERE (META_KEYWORDS LIKE '% ')
That seems to work but I'd like to be certain. It seems too simple.
keyword= listGetAt(meta_keywords,1)
CF error was:
"In function ListGetAt(list, index [, delimiters]) the value of index, which is 1, is not a valid index for the list given as a the first argument (this list has 0 elements). Valid indexes are in the range 1 through the number of elements in the list."
Meta_keywords is a comma delimited list of keywords. I believe the error is thrown from a trailing comma at the end of the last meta_keyword, sometimes with an extra space, such as:
'one, two, three, '
How can I indentify a trailing comma in the sql 2000 database using an sql query? I'm not sure how to construct such an sql query and remove it.
To find the trailing comma:
SELECT META_KEYWORDS
FROM AREAS
WHERE (META_KEYWORDS LIKE '%,')
To find the trailing space:
SELECT META_KEYWORDS
FROM AREAS
WHERE (META_KEYWORDS LIKE '% ')
That seems to work but I'd like to be certain. It seems too simple.
here is an example how to update
declare @char varchar(2000)
SET @char = 'one, two, three '
SELECT @char = SUBSTRING(@char, 1, CASE WHEN charindex( ',',RTRIM(@char),len(RTRIM
SELECT @char
declare @char varchar(2000)
SET @char = 'one, two, three '
SELECT @char = SUBSTRING(@char, 1, CASE WHEN charindex( ',',RTRIM(@char),len(RTRIM
SELECT @char
ASKER
I didn't understand any of your update example. My fault, not yorus. I wouldn't want to update with something I don't understand. But that's not important. What I wanted to know is if you believe the approach I used was the best to find the trailing comma. If it is then I'll use it to find the space. I know it works but is it the best method for a simple query in enterprise manager?
If you have the option to handle this at the front end, then that will be fine, because string operations will need so much resources..
Now on the above query, it wont do anyupdate, it just removes the trailing commas, did you run it on the Query Analyzer ? Check it ?
Now if you want only a search , then your approch is ok. but you need to put a comma after the % sign in your query, as i shown in my first post...
Now on the above query, it wont do anyupdate, it just removes the trailing commas, did you run it on the Query Analyzer ? Check it ?
Now if you want only a search , then your approch is ok. but you need to put a comma after the % sign in your query, as i shown in my first post...
ASKER
Yes, I understand the space, the comma, etc.
WHERE (META_KEYWORDS LIKE '% ')
WHERE (META_KEYWORDS LIKE '%, ')
But I'm not getting the response I need, which is a real commitment on the method, instead of just an "okay".
> your approch is ok
I know it's okay because it works. What I was hoping from you, or someone, was to tell me if using that query was the best or only approach. Just saying ok doesn't give me anything new for this question. It just leaves me where I was before I asked the question.
So, what do you think? Is the approach, the idea, is it the best method, the only method, both? That's all I need to know. And again, I udnerstand what needs to come after the % character.
I don't need to run an update because I manually removed the trailing commas and spaces and they won't be generated again.
Thanks for your help.
WHERE (META_KEYWORDS LIKE '% ')
WHERE (META_KEYWORDS LIKE '%, ')
But I'm not getting the response I need, which is a real commitment on the method, instead of just an "okay".
> your approch is ok
I know it's okay because it works. What I was hoping from you, or someone, was to tell me if using that query was the best or only approach. Just saying ok doesn't give me anything new for this question. It just leaves me where I was before I asked the question.
So, what do you think? Is the approach, the idea, is it the best method, the only method, both? That's all I need to know. And again, I udnerstand what needs to come after the % character.
I don't need to run an update because I manually removed the trailing commas and spaces and they won't be generated again.
Thanks for your help.
ASKER
I've not heard back from you so thought I'd ask once more:
Is the approach the best method you can think of, or the only method? I need to know how much to trust this method so I can consider the answer. Is the query to find the comma and space a true and trustable method. A yes or no would be good, or a percentage. Please answer and I'll close this question and give the points.
Is the approach the best method you can think of, or the only method? I need to know how much to trust this method so I can consider the answer. Is the query to find the comma and space a true and trustable method. A yes or no would be good, or a percentage. Please answer and I'll close this question and give the points.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No, that does it. Just wanted to clean up this question, get the answer, and get you the points. And let you get back to sleep. Sweet dreams...
thnaks ... now i am in the office
>How can I indentify a trailing comma in the sql 2000 database using an sql query?
SELECT META_KEYWORDS
FROM AREAS
WHERE (META_KEYWORDS LIKE '%, ') --- put a space after the comma