Solved

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

Posted on 2007-03-27
9
182 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

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, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now