?
Solved

How do I find ALL the positions of a certain character in a string?

Posted on 2007-03-27
9
Medium Priority
?
192 Views
Last Modified: 2010-03-19
I am trying to find the positions of all the $ in the summary string.  I'm just doing a simple select and I am getting an error that loc1, loc2, loc3 are invalid columns.  Hellllp!  

SELECT [Case ID], Summary,
charIndex('$',[summary],1) as loc1 ,
charindex('$',[Summary],(loc1 + 1)) as loc2
charindex('S',[Summary],(loc2 + 1)) as loc3
FROM [tblRemedy Tickets]
WHERE Item Like '%User%' AND Summary Like '%$%';

summary = "CTSU$CSBBWWW$WCAROL$"
0
Comment
Question by:CaroleTSullivan
[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
  • 3
9 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 18802330
you are missing a comma after loc2

charindex('$',[Summary],(loc1 + 1)) as loc2 ,
0
 

Author Comment

by:CaroleTSullivan
ID: 18802363
Thanks anyway but

I figured it out.....to find all occurrences, I need to imbed the previous charindex.....For example:

SELECT [Case ID], Summary,
charIndex('$',[summary],1),
charindex('$',[summary],charIndex('$',[summary])+1)
FROM [tblRemedy Tickets]
WHERE Item Like '%User%' AND Summary Like '%$%'
0
 

Author Comment

by:CaroleTSullivan
ID: 18802379
how do I close this question since I figured it out?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:CaroleTSullivan
ID: 18802737
actually, my answer did work either.  I have to find all 8 occurrences using charindex.  When I name the outcome as AS, I still get the invalid column error.  Help anyone?
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 18802763
>> When I name the outcome as AS

not sure what you mean by this -- you want the resulting column to be named "AS" ?

if so, then do this:  as [AS]
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 18802789
... or are you saying you are having trouble aliasing the charindex columns?

SELECT [Case ID], Summary,
charIndex('$',[summary],1) as [loc1],
charindex('$',[summary],charIndex('$',[summary])+1) as [loc2]
FROM [tblRemedy Tickets]
WHERE Item Like '%User%' AND Summary Like '%$%'
0
 

Author Comment

by:CaroleTSullivan
ID: 18804061
yes, when I use the alias for the column names, I get an error that the column name is invalid.
0
 
LVL 25

Accepted Solution

by:
DBAduck - Ben Miller earned 1000 total points
ID: 18805992
The only way that I know of to get all occurences is to use it in a loop and keep track of all the indexes.

You could do it in a FUNCTION and return a string with index,index,index and it would become a column in your output.

This little snippet should be able to help you run this:

DECLARE @index int
SET @index = -1
DECLARE @return varchar(100)
DECLARE @value varchar(100)
SET @value = 'NaNaNaNaNaNa'
SET @return = ''

WHILE @index > 0 OR @index = -1
  BEGIN
      SELECT @index = CHARINDEX('N', @value, @index+1)
      IF @index > 0
        BEGIN
            SELECT @return = @return + ',' + CAST(@index as varchar)
        END
  END

SELECT SUBSTRING(@return, 2, LEN(@return)-1)

So you would just put this in a function that RETURNS varchar(50) or whatever and RETURN (@return) and the @value should be the one that you pass in.

Say your FUNCTION name is GetIndexesFromValue, then your query would be:

SELECT [Case ID], GetIndexesFromValue(Summary) as Summary,
FROM [tblRemedy Tickets]
WHERE Item Like '%User%' AND Summary Like '%$%'

Hope that helps.

0
 

Author Comment

by:CaroleTSullivan
ID: 18808500
thanks, that's what I ended up doing.  Have a great day!
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

777 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