Can't add a string to the end of a string

Question

I have this query:

SELECT distinct
  substr(p1.name,-1) as index_
  p2.value as value        
FROM pks p  
  LEFT JOIN Parameter p1 on p1.ID=p.id AND p1.NAME like '%TFrom%'
  LEFT JOIN Parameter p2 on p2.ID=p.id AND p2.NAME like '%TFrom%' + substr(p1.name,-1)

WHERE p.type='singer'

What I want to do is to add the index_ value to the end of the p2.NAME string. Sometimes the index_ is (null), but I don't belive that It's a problem, as when it's an empty string it just should pass without adding somthing to the p2_NAME string

In SQL server it can be done by adding this after the Join:

+(SUBSTRING(p1.NAME,[length], len(p1.PA_NAME)) )

But in Oracle it won't add a string to string. I'm sure that I'm missing somthing really small.

See here: http://stackoverflow.com/questions/278189/what-is-the-string-concatenation-operator-in-oracle — Guffa, Feb 18 '13 at 09:58

score 2 · Accepted Answer · answered Feb 18 '13 at 09:57

2

The concatenation in Oracle is done with ||:

p2.NAME like '%TFrom%' || substr(p1.name,-1)

answered Feb 18 '13 at 09:57

Florin Ghita

17,525
6
57
76

It's not working. :( I'm getting an invalid identifier error – Slim Feb 18 '13 at 11:36
You have something different wrong. The above syntax is ok. – Florin Ghita Feb 18 '13 at 11:41

score 2 · Answer 2 · answered Feb 18 '13 at 09:58

You can concatenate strings with either || or concat:

select last_name || '''s job category is ' || job_id
FROM employees 
WHERE employee_id = 152;

or

SELECT CONCAT(CONCAT(last_name, '''s job category is '), job_id) "Job" 
FROM employees 
WHERE employee_id = 152;

Can't add a string to the end of a string

2 Answers2