InStrRev in T-SQL? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

InStrRev in T-SQL?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-11-02 : 17:17:29

Mario writes "Hello.

This is my question.

Does exists, or is there a way of simulating it, the equivalent to VBScript's InStrRev function in T-SQL?

My problem is as follows:

I have a field in an SQL7 table that constains user names in any of the following ways:

full_name
--------------------
John F. Smith
Peter Doe
Jane Sue Gates
Michael
Jeremy Acton

That is, there's no consistency in names. Some names only have the first name, others have middle initial, middle name, more than 1 space between first and last, and so on..

It is very easy with InStrRev to find the last space and getting the string beginning from that caracter, in order to separate last name from first and middle names, but such function doesn't exists in T-SQL (???).

I need to insert all the names in a new tables, but separating first and last name. For example, using above names:

last first
--------------- ----------------
Smith John F.
Doe Peter
Gates Jane Sue
Michael
Acton Jeremy

I need to make such task in a single SQL statement

Thanks in advance.

Mario"

jpoberempt
Starting Member

1 Post

Posted - 2009-03-25 : 16:00:00

You need to create InStrRev as a User Defined Function

Example:
CREATE FUNCTION dbo.InStrRev (@StringCheckIn VARCHAR(254),@StringCheckFor VARCHAR(254))
RETURNS INT AS
BEGIN
Declare @StringHere INT
Declare @SpotCount INT
Declare @LookAtChars VARCHAR(254)
Declare @LengthOfStringCheckIn INT
Declare @LengthOfStringCheckFor INT
SET @StringCheckIn = LTrim(RTrim(@StringCheckIn))
SET @StringCheckFor = LTrim(Rtrim(@StringCheckFor))
SET @LengthOfStringCheckIn = Len(@StringCheckIn)
SET @LengthOfStringCheckFor = Len(@StringCheckFor)
SET @SpotCount = @LengthOfStringCheckIn - @LengthOfStringCheckFor
WHILE @SpotCount > '1'
BEGIN
SET @LookAtChars = Right(Left(@StringCheckIn,@SpotCount),@LengthOfStringCheckFor)
IF @LookAtChars = @StringCheckFor
Begin
SET @StringHere = @SpotCount
BREAK
End
Else
Begin
SET @SpotCount = @SpotCount - 1
CONTINUE
End
END

RETURN @StringHere
END

End Of Example...

To test the function run the following in Query Analyzer

Declare @string1 VARCHAR(254)
Declare @string2 VARCHAR(254)
Declare @PosString INT

Set @string1 = 'This is the string we are checking for the word are from the right side of the string.'
Set @string2 = 'are'

Set @PosString = dbo.InStrRev(@string1,@string2)

Print @PosString

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-03-25 : 16:31:03

Any reason you'd resurrect a topic that is 8 1/2 years old?

Terry

-- Procrastinate now!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-25 : 16:35:29

Its not like you have a similar question...you gave a solution for a 8.5 year old problem....thats funny ...lol

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources