CODEWITHSUNDEEP
sqloracle-databasewhere-clause
Club323
Club323

Reputation: 23

SQL - Ignore the first character of a field's text

Let me lay out what some of these fields look like before I explain what I need to do...

|---------------------|------------------|------------------|
|      T1.CustID      |   T2.CustID      |   T2.ExtCustID   |
|---------------------|------------------|------------------|
|          1234       |       1234       |         A1234    |
|---------------------|------------------|------------------|

So I need to build a where statement that basically ignores the first letter in the ExtCustID and tries to find the same ID in T1.CustID

T1 and T2 are joined via CustID (inner-join) but I can't figure the where statement out...

I've tried stuff like where T1.CustID like (right(T2.ExtCustID and where T1.CustID like (substring(T2.ExtCustID but I'm not sure I can use RIGHT/SubString/TRIM to do this... Can anyone point me in the right direction/give me an example?

NOTE - I'm using Oracle!

Upvotes: 0

Views: 628

Answers (4)

SQLCliff
SQLCliff

Reputation: 181

RIGHT(T2.ExtCustID, LEN(T2.ExtCustID) -1 )

This returns the x rightmost characters of ExtCustID, where x is the LEN (number of characters) of ExtCustID, minus 1.

Upvotes: 0

Priyanka
Priyanka

Reputation: 7

Use below statement Right(fieldname, len(fieldname)-1)

Upvotes: 0

Club323
Club323

Reputation: 23

SQLCliff ended up giving me the stepping stones to the solution. For some reason I wasn't really thinking about the length...

I also didn't update the OP and say that I'm using Oracle... All my fault not yours!

But here's the solution I fixed up -

where not(t1.CustID like substr(t2.customer_external.external_customer_code,length(t2.customer_external.external_customer_code)- 5))

Upvotes: 0

access_granted
access_granted

Reputation: 1917

where T1.CustId=substr(T2.ExtCustId,2)

Upvotes: 1

Related Questions