charindex question

I have a field containing a string with '/' in it multiple times.

How can I return the charindex of the last occurance of '/' in the
string?

Regards,
Ciarndeclare @.s varchar(10)
set @.s='as/gf/af/h'
select len(@.s)-charindex('/',reverse(@.s))+1

Madhivanan

CHARINDEX and STUFF/REPLACE

Hello,

I need to be able to replace only the first occurance of a space character
in a column.
Reason being is the data in the column I am trying to replace seems to have
umpteen space characters after each entry, so a simple replace function
will replace all the spaces after it with what I want!

I have thought of RTRIM to get rid of the spaces after and then replace, I
have also thought of CHARINDEX to find the first occurance of a space and
STUFF to replace it.
I have done my homework on these functions!
But I am having trouble writing such a statement,
I've never written a query which would use more then one function on one
column you see and I am getting confused!

I'll tell you what I want to do in simple steps

Replace only the first found space in a name column, but then if a name has
a middle initial that will be a problem,
Replace that with a dot.
then concatanate '@.emailaddress;@.emailaddress2' after it
so when SQLServer does the select it will bring back something like

joe.bloggs@.emailaddress;emailaddress
But I guess I'd also need joe.n.bloggs@.emailaddress;emailaddress

The data in the column looks like this at the moment

joe bloggs
But I guess there may come a time when we have
joe n bloggs, just to complicate things!

What is your advice, and how do I write a query like this
I have been playing around with it in Query Analyser but as I said I am
getting confused and need some help if you don't mind

Thanks a lot to all who reply :-)

Regards

JayneHi

Try:

SELECT CHARINDEX(SPACE(1),[String] ) AS [Position],[String],
STUFF ( [String], CHARINDEX(CHAR(32),[String] ), 1, SPACE(0) ) AS
[NewString]
FROM ( SELECT 'ABC DE FG' AS [String]
UNION ALL SELECT ' ABC DE FG'
UNION ALL SELECT ' ABC DE FG'
UNION ALL SELECT 'ABCDEFG'
) A

As an update statement

UPDATE MyTable
SET [String] = STUFF ( [String], CHARINDEX(CHAR(32),[String] ), 1,
SPACE(0) )
WHERE CHARINDEX(CHAR(32),[String] ) > 0
John

"Little PussyCat" <SPAMSPAM@.NOSPAM.com> wrote in message
news:v7ibg2-thj.ln1@.tiger.sphynx...
> Hello,
> I need to be able to replace only the first occurance of a space character
> in a column.
> Reason being is the data in the column I am trying to replace seems to
> have
> umpteen space characters after each entry, so a simple replace function
> will replace all the spaces after it with what I want!
> I have thought of RTRIM to get rid of the spaces after and then replace, I
> have also thought of CHARINDEX to find the first occurance of a space and
> STUFF to replace it.
> I have done my homework on these functions!
> But I am having trouble writing such a statement,
> I've never written a query which would use more then one function on one
> column you see and I am getting confused!
> I'll tell you what I want to do in simple steps
> Replace only the first found space in a name column, but then if a name
> has
> a middle initial that will be a problem,
> Replace that with a dot.
> then concatanate '@.emailaddress;@.emailaddress2' after it
> so when SQLServer does the select it will bring back something like
> joe.bloggs@.emailaddress;emailaddress
> But I guess I'd also need joe.n.bloggs@.emailaddress;emailaddress
> The data in the column looks like this at the moment
> joe bloggs
> But I guess there may come a time when we have
> joe n bloggs, just to complicate things!
> What is your advice, and how do I write a query like this
> I have been playing around with it in Query Analyser but as I said I am
> getting confused and need some help if you don't mind
> Thanks a lot to all who reply :-)
> Regards
> Jayne|||Little PussyCat wrote:

> Hello,
> I need to be able to replace only the first occurance of a space character
> in a column.
> Reason being is the data in the column I am trying to replace seems to
> have umpteen space characters after each entry, so a simple replace
> function will replace all the spaces after it with what I want!

Its ok, I've found a way to do it.
firstly I don't want extra spaces after each name anyway so I do

Update [tablename]
SET [columnname] = RTRIM(Columnname]

Then I just do SELECT REPLACE(ColumnName,' ','.') +
'@.emailaddress;@.emailaddress'
which replaces all of the spaces in the name column and puts a dot in and
also takes into account people's names who have a middle initial! then
appends the rest of the email address in.

Thanks anyway,

Jayne