SQL string connection function (MSSQL MySQL Oracle)

Source: Internet
Author: User
Tags mssql mysql tutorial

SQL string connection function (MSSQL MySQL tutorial Oracle)
MySQL String connection concat function
How to use:
Concat (STR1,STR2,...)

MySQL appends a string to a field in the table:
UPDATE table_name set FIELD=CONCAT (field, ', str ')

MySQL adds a string before a field in the table
UPDATE table_name set FIELD=CONCAT (' str ', field)

Returns a string resulting from a connection parameter. If any of the arguments are null, the return value is null.


Oracle string Join functions

SELECT *
From a
Where (substr (value, 0, 2) | | '-' || substr (Value, 2, 5) | | '-' ||
substr (Value, 8, 9)) not in (select B from data);
or update value directly

Update a set value= (substr (value, 0, 2) | | '-' || substr (Value, 2, 5) | | '-' ||
substr (Value, 8, 9));
If it's not Oracle, SUBSTR replace substring.

MS SQL string Connection

create   table   Test (id   int,txt   varchar)
insert   Test
select   1, ' AAA '    union   all
select   1, ' BBB '    Uni on   all
select   2, ' CCC '    union   all
select   3, ' DDD ' &N bsp;  union   All
select   3, ' eee '    union   all
Select &nb Sp 3, ' FFF '
--select   *   from   test
Go

Create function gettxt (@id int)
Returns varchar (8000)
As
Begin
DECLARE @s varchar (8000)
Set @s= '
Select @s=@s + ' +txt from test where id= @id
--return @s
Return stuff (@s,1,1, "")
End
Go

Select Id,dbo.gettxt (ID) txt from test GROUP by ID
Go

Drop function Gettxt
DROP TABLE Test

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.