Solved

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

Posted on 2007-03-27
9
190 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 24

Accepted Solution

by:
DBAduck - Ben Miller earned 250 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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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…

705 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