What Does the Pipe Operator Do in SQL

Use of pipe symbol in Select clause

If you are asking whether you can use pipes || for concatenation in Microsoft SQL, then the short answer is no.

If you’re asking about the concatenation operator itself, then read on.

|| is the standard ANSI concatenation operator. This is apparent in PostgreSQL, SQLite and Oracle, among others.

Microsoft, however uses +, because, why not. Except Microsoft Access uses &, because, why not.

MariaDB/MySQL have two modes. In traditional mode, || is interpreted as “or”, and there is no concatenation operator. In ANSI mode, || is interpreted as the concatenation operator.

Most DBMS (not SQLite) have the non-standard concat() function which will also concatenate. They also coalesce any NULLs to empty strings, so they’re a bit more forgiving if you don’t care about NULLs.

What does the pipe/veritcal bar character mean in TSQL?

That is a bitwise OR

http://msdn.microsoft.com/en-us/library/ms176122.aspx

In Oracle SQL, what is a single pipe character ( '|' ) used for?

The documentation only shows it as a bitwise OR operator in TimesTen:

Bitwise OR of the two operands.
Sets a bit to 1 if one or both of the corresponding bits in Expression1 and Expression2 are 1. Sets a bit to 0 if both of the corresponding bits are 0.

But you didn't mention TimesTen, and it isn't valid in 'normal' SQL or PL/SQL.

What does single quote character and pipe symbol stand for

The single quote character (') starts and ends a string.

The double pipe symbol (||) concatenates strings.

As per some comments ||, is not available with MySQL.

Now, your statement contains even triple quote characters ('''). This is because in order to have a single quote character within a (quoted) string, it needs to be escaped by another (preceeding) single quote character.

So: 'foo' is the string foo,'bar' || ' ' || baz is the string bar baz and It''s ok is the string It's ok.

The select statement in your question returns a result set constisting of one column whose data type is string. These strings look like so:

delete from TABLE_1 TABLE_1.ID ='ABCD';
delete from TABLE_1 TABLE_1.ID ='DEFG';

whereas the ABCD, DEFG ... are the values of v.id (that is of table_2 column id).

It looks like your select statement is used to generate delete statements that in turn must somehow be run. Of course, those crated statement won't execute as a WHERE is missing.

What does double bars (||) mean in SQL?

double bars are concatination:

select 'hello' || ' ' || 'world' from dual;

yields

'hello world'

Why does MySQL refuse pipe ('|') character in string on INSERT INTO

On my machine, this works fine:

CREATE TABLE TerminalEventChild (id INT, stringValue VARCHAR(200));

INSERT INTO TerminalEventChild (id,stringValue) VALUES
(64,'version123|');

Probably, your client treats the pipe character specially.

What client do you use?

---

Related Topics