quote:

---

SELECT 'HeLlO wOrLd!' = 'hElLo WoRlD!' AS TextCompare -- this is true
CAST('HeLlO wOrLd!' AS VARBINARY) = CAST('hElLo WoRlD!' AS VARBINARY) AS BinaryCompare -- this is false

This help?

---

'*******Get Name Without JR, SR, III, IIII******************
    NameExt = Right$(FullName, 3)
    If NameExt = "<IV" Or NameExt = "<JR" Or _
    NameExt = "<SR" Or NameExt = "<II" Then
        FullName = Left(FullName, NameLen - 3)
    End If
    NameExt = Right(FullName, 4)
    If NameExt2 = "<III" Then
        FullName = Left(FullName, NameLen - 4)
    End If 
'***Find out How Many <s are in Name**************************
    LessThan1 = InStr(1, FullName, "<", vbTextCompare)
    LessThan2 = InStr(LessThan1 + 1, FullName, "<", vbTextCompare)
    If LessThan2 > 0 Then
        HowManyLess = 2
    Else
        HowManyLess = 1
    End If

quote:

---

vbTextCompare isn't a case sensitive mode, is it?

You can do all this stuff with CharIndex or PatIndex functions...

Jay White
{0}

---

quote:

---

Here's your code converted to T-SQL ...

--*******Get Name Without JR, SR, III, IIII******************
SET @NameExt = RIGHT(@FullName, 3)
IF NameExt IN ("<IV", "<JR", "<SRC", "<II") SET @FullName = LEFT(@FullName, @NameLen - 3)
SET @NameExt = RIGHT(@FullName, 4)
IF @NameExt2 = '<III' SET @FullName = LEFT(@FullName, @NameLen - 4)

--***Find out How Many <s are in Name**************************
SET @LessThan1 = CHARINDEX(@FullName, '<')
SET @LessThan2 = CHARINDEX(@FullName, '<', @LessThan1 + 1)

IF @LessThan2 > 0
SET @HowManyLess = 2
ELSE
SET @HowManyLess = 1

on a side note ... your vbTextCompare does nothing special in your vb code and is pretty much just extra space being used up....

---

CARL A<JONES<& ANGIE MYER

DECLARE @NAMEEXT AS CHAR(3), @NAMEEXT2 AS CHAR(4), @LESSTHAN1 INT, @LESSTHAN2 INT,
        @HOWMANYLESS INT, @FULLNAME AS CHAR(60), @NAMELEN INT, @LT AS CHAR(3), @AMP INT,
        @HOWMANYAMP INT, @PRIMLNAME AS CHAR (30), @LFTCARET CHAR(1), @PRIMFNAME CHAR(25),
        @HELP AS CHAR (30)

SET @FULLNAME = 'CARL A<JONES<& ANGIE MYER'
SET @LFTCARET = '<'
SET @LT = '<'
SELECT REPLACE(@FULLNAME, @LT, '<')

SET @NAMELEN = LEN(@FULLNAME)

--*******Get Name Without JR, SR, III, IIII****************** 
SET @NameExt = RIGHT(@FullName, 3) 
IF @NameExt IN ("<IV", "<JR", "<SRC", "<II") 
  SET @FullName = LEFT(@FullName, @NameLen - 3) 
  SET @NameExt = RIGHT(@FullName, 4) 
IF @NameExt2 = '<III' 
  SET @FullName = LEFT(@FullName, @NameLen - 4) 

--***Find out How Many <s are in Name************************** 
SET @LessThan1 = CHARINDEX(@FullName, '<', 1) 
SET @LessThan2 = CHARINDEX(@FullName, '<', @LessThan1 + 1) 

IF @LessThan2 > 0 
  SET @HowManyLess = 2 
ELSE 
  SET @HowManyLess = 1 

--***Find out how many &s are in Name*******************************************
SET @AMP = CHARINDEX(@FULLNAME, '&', 1)
IF @AMP = 0 
  SET @HOWMANYAMP = 0
ELSE
  SET @HOWMANYAMP = 1

--***SET VARIABLE NAMES = NAMEL1 ***********************************************
IF @HOWMANYLESS = 1 AND @HOWMANYAMP = 0 
  SET @PRIMLNAME = RIGHT(RTRIM(@FULLNAME), LEN(@FULLNAME) -(CHARINDEX('<', @FULLNAME))-@LESSTHAN1)
  SET @PRIMFNAME = LEFT(RTRIM(@FULLNAME), CHARINDEX('<', @FULLNAME)-1)

IF @HOWMANYLESS = 1 AND @HOWMANYAMP = 1
  SET @PRIMLNAME = RIGHT(RTRIM(@FULLNAME), LEN(@FULLNAME) -(CHARINDEX('<', @FULLNAME)))  
  SET @PRIMFNAME = LEFT(RTRIM(@FULLNAME), CHARINDEX('<', @FULLNAME)-1)    

CARL A<JONES<& ANGIE MYER

(1 row(s) affected)

HOW MANY < = 1
HOW MANY & = 0
THE FULL NAME IS = CARL A<JONES<& ANGIE MYER     
THE LAST NAME IS = JONES<& ANGIE MYER            
THE FIRST NAME IS = CARL A                   
THE NEW NAME IS = CARL A JONES<& ANGIE MYER

quote:

---

It is not complete but I Hope this is of some help

---

It was of great help! THANK YOU! It worked as I thought it should. After implementing it, I was told that I need to return 4 variables after reading in one. So I've been working on revising it. Here is the code I have so far.

Test data: 1)JOHN M<BROWN 2)HENRY A<CARTER 3)FRANK N<DE PORTA 4)FLORENCE E<JONES 5)JAMES R<ODONNELL 6) LILLIE B<OWEN-SMITH 

DECLARE @InString varchar(35) 
SET @Instring = 'FLORENCE E<JONES' 

DECLARE @MidPosition tinyint, @AmpCt int, @LastSpace int, @Less1 int, @Less2 int, @Amp1 int,
@CharPos int, @TmpName varchar(35), @char varchar(1), @Kind varchar(1),
@PLName varchar(20), @PFName varchar(15), @SLName varchar(20), @SFName varchar(15)

SET @MidPosition = 0 SET @AmpCt = 0 SET @LastSpace = 0 set @less1 = 0 set @less2 = 0 
set @amp1 = 0 set @CharPos = 0 SET @TMPNAME = '' SET @CHAR = '' SET @KIND = ''
SET @PLNAME = '' SET @PFNAME = '' SET @SLNAME = '' SET @SFNAME = ''  

--get the field positions for the '<' and the '&' and set the appropriate variables
WHILE @MidPosition < LEN(@InString)
begin
  set @char = SUBSTRING(@Instring,@midposition,LEN(@Instring))
  if @char = '<'and @less1 = 0
  begin
    set @less1 = @midposition 
    set @kind = @char
    print "The first < is located in position: " + cast(@less1 as varchar)
  end
  if @char = '<' and @midposition > @less1 and @less2 = 0
  begin
    set @less2 = @midposition
    print "The second < is located in position: " + cast(@less2 as varchar)
  end
  if @char = '&'
  begin
    set @amp1 = @midposition
    print "The & is located in position: " + cast(@amp1 as varchar)
  end
  IF SUBSTRING(@InString,@MidPosition,1) = ' ' 
  BEGIN 
    SET @LastSpace = @MidPosition 
  END 
  set @midposition = @midposition + 1
end 

--ONE '<' AND ZERO '&'
if @LESS1 <> 0 AND @less2 = 0 and @amp1 = 0
begin
  if @lastspace < @less1
  begin
    set @PLName = right(@instring, len(@instring) - @less1)
    set @PFName = left(@instring, (@less1 -1))
    set @SLName = ''
    set @SFName = ''
    print "Stopped at 1st if!"
  end
  if @lastspace > @less1
  begin
    set @PLName = right(@instring, len(@instring) - @less1)
    set @PFName = left(@instring, (@less1 -1))
    set @SLName = ''
    set @SFName = ''
    print "Stopped at 2nd if!"
  end
end
print @kind
print "@LESS1 = " + CAST(@less1 AS VARCHAR)
print "@LESS2 = " + CAST(@LESS2 AS VARCHAR)
print "@AMP1 = " + CAST(@Amp1 AS VARCHAR)
PRINT "@LASTSPACE = " + CAST(@LASTSPACE AS VARCHAR)
PRINT "FLORENCE E<JONES"
print "PRIM LAST NAME IS: " + @PLName
Print "PRIM FIRST NAME IS: " + @PFName
Print "SEC LAST NAME IS: " + @SLName
Print "SEC FIRST NAME IS: " + @SFName

The procedure works NOW!

Thanks for your help!
Teresa

Edited by - TJ on 09/18/2002 12:47:23