Ilmu Sql

Berikut contoh simpel penggunaan replace di sql server:

select REPLACE
(
    path,
    (select path from MStructureOrganisation where id=(select parentid from MStructureOrganisation where id=128)),
    (select path from MStructureOrganisation where id=192)
)
from MStructureOrganisation where path like '0;0000375;0001099;0001099;0000542%'

WITH n(IdCovJob, idemp, startdate, enddate) AS 
(
    select idcovjob, idemp, startdate, enddate
    from MCovjobemployee --where IdEmp='1576'
    union all
    SELECT x.idCovJob, n.idemp, x.activeddate, x.enddate
    FROM MCoverJob x WITH (NOLOCK), n
    WHERE n.IdCovJob = x.IdCovJobPar
)
insert into TempEmpCovjob (IdCovJob, idemp, startdate, enddate)
select * from (
    select * from n
)x

SELECT * FROM sysmessages where msglangid=1033

Misalnya kita punya data:
123,345,678
234,567,789

dan kita ingin menampilkan data
123
345
678
234
567
789

Berikut script sederhana untuk men-split data.

select requester, b.Value approver, settingapproval_step from (
    select requestby_posid requester, approvedby_posid approver, settingapproval_step
    FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').anugerah_prod.dbo.vw_request_approval a
    where requestapproval_id=3 and requestby_posid=15351 and requestby_posid not in
    (
        select position_id FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').anugerah_prod.dbo.thrmposition where position_parentpath like '%11361%'
    ) and approvedby_posid not in( cast (14120 as varchar(100)) )--HR Officer   
) a
    cross apply
    (
        select  *  from  dbo.fn_split(a.approver, ',')
    ) b
    inner join (select * from OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').anugerah_prod.dbo.vw_employee_info) c on (b.Value=c.position_id)
order by settingapproval_step

USE [ssreport]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_Split]    Script Date: 08/29/2012 10:54:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER function [dbo].[fn_Split](
@String nvarchar (4000),
@Delimiter nvarchar (10)
)
returns @ValueTable table ([Value] nvarchar(4000))
begin
declare @NextString nvarchar(4000)
declare @Pos int
declare @NextPos int
declare @CommaCheck nvarchar(1)

--Initialize
set @NextString = ''
set @CommaCheck = right(@String,1)

--Check for trailing Comma, if not exists, INSERT
if (@CommaCheck <> @Delimiter )
set @String = @String + @Delimiter

--Get position of first Comma
set @Pos = charindex(@Delimiter,@String)
set @NextPos = 1

--Loop while there is still a comma in the String of levels
while (@pos <> 0)
begin
set @NextString = substring(@String,1,@Pos - 1)
insert into @ValueTable ( [Value]) Values (@NextString)
set @String = substring(@String,@pos +1,len(@String))
set @NextPos = @Pos
set @pos = charindex(@Delimiter,@String)
end

return
end

GO