Solved

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

Posted on 2007-03-27
9
186 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

828 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