substring question.

[alsjka]

Question please:

I am trying to gather info like below. The issue is i have a record used with the substring that i need to compare the first 2 characters instead of 1. Could someone lead me in the right direction please.

How would I change this to be able to compare 12name or 2name.

SUBSTRING(catalognum, 1, (LEN(catalognum) - 1)) = SUBSTRING(emp.email, 2, (LEN(emp.email) - 18))

SELECT DISTINCT PURREQ.CATALOGNUM,PURREQ.ITEMNUM, PURREQ.DESCRIPTIONONPO, PURREQ.PONUM,PURREQ.VENDORNAME, purreq.qtyrequested, purreq.dategenerated, purreq.total, po.PURCHASINGCENTERID,emp.email ,purreq.seqnum
FROM PURREQ,PO, EMP
WHERE PURREQ.PONUM = PO.PONUM and SUBSTRING(catalognum, 1, (LEN(catalognum) - 1)) = SUBSTRING(emp.email, 2, (LEN(emp.email) - 18))
and PO.STATUS = 'Open'
and PURREQ.QTYRECEIVED is null
and purreq.invytype != 'serv'
and purreq.siteid = emp.siteid
and (PURREQ.CATALOGNUM != '' and PURREQ.CATALOGNUM is not null and PURREQ.CATALOGNUM != 'none')
GROUP BY PURREQ.CATALOGNUM, PURREQ.ITEMNUM, PURREQ.DESCRIPTIONONPO, purreq.ponum,PURREQ.VENDORNAME,PURREQ.QTYRECEIVED,purreq.qtyrequested, purreq.dategenerated, purreq.total, po.PURCHASINGCENTERID,emp.email,purreq.seqnum
order by purreq.ponum

 

[SkipVought]

Hi,

...substring that i need to compare the first 2 characters

Substr(MyString,1,2)

is the first 2 characters of MyString

Skip,
[sub]
[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen![/sub]

 

[alsjka]

Hi..I guess I did not explain well..

I have the following data i would like to compare.

Lastname, first initial - catalognum

smithr
smitht
reissec - This one has two first initials that need to be compared instead on just one initial.

Against the following - emp.email

rsmith@tonnes-thine.com
tsmith@tonnes-thine.com
ecreiss@tonnes-thine.com


The following only works with the first initial. This only works so so I need to compare the catalognum again the emp.email i think i need to flip the catalognum's 1st or 2nd initials then compare that against the characters to the left of the @ sign in the emp.email.


WHERE PURREQ.PONUM = PO.PONUM and SUBSTRING(catalognum, 1, (LEN(catalognum) - 1)) = SUBSTRING(emp.email, 2, (LEN(emp.email) - 18))


I hope I explained it better. Any help is great! Thanks

 

[SkipVought]

So what is the LOGIC that determines whether there are one or two initials in the name string?

reissec - This one has two first initials that need to be compared instead on just one initial.

Skip,
[sub]
[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen![/sub]