Solved

Find trailing comma and space in sql query

Posted on 2006-07-06
9
2,141 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:RollinNow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17049076
RollinNow,
>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
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17049118
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(@char))) > 0 THEN  charindex( ',',RTRIM(@char),len(RTRIM(@char)))-1 ELSE LEN(@char) END)
SELECT @char
0
 

Author Comment

by:RollinNow
ID: 17053040
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?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17055977
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...
 

0
 

Author Comment

by:RollinNow
ID: 17060784
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.
0
 

Author Comment

by:RollinNow
ID: 17084544
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.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 250 total points
ID: 17084629
Sorry for the delayed reply, actually i am getting nearly 150 mails from EE, some i accidently deletes, that's why i couldn't reply you in time

>Is the approach the best method you can think of, or the only method?
The best thing will be to handle this at the front end, so that no unnecessary commas and spaces will passed from the front end. Also remember String operations in sql server are costly in sql server

If you use the like statement like   'LIKE '%, '   as in your query, it wont use any kinds of indexes, it will use a table scan to fetch the records...

>Is the query to find the comma and space a true and trustable method

Yes, since  all the records will be inserteed in this way, it is trustable ..

Its nearly 1.00 am here, i am going to bed now.. In case you have any further queries , post here ; i will reply after 7 hours ..

0
 

Author Comment

by:RollinNow
ID: 17087830
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...
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17087943
thnaks ... now i am in the office
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Consolidate rows 3 26
SQL Server Trigger 8 37
Convert time stamp to date 2 56
Make an array show the subkey and put it in a query 2 24
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question