Solved

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

Posted on 2007-03-27
9
188 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
export sql server dbs 2 26
SQL: Transformation or Pivot 3 34
Creating Scalar Function 3 17
How can I group this data in the following manner? 2 24
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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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 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…

732 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