*my little notes*

Thursday, 5 April 2012

Monday, 19 December 2011

combine string and number like 1.1

declare @t table(id int ,data varchar(50))
declare @t3 table(id int, data3 varchar(50))
insert into @t
select '1','Rak 10' union all
select '2','Rak 1.2' union all
select '3','Rak 122' union all
select '4','Rak 2' union all
select '5','Area 2'union all
select '6','Area Lantai 1' union all
select '7','Rak 1'

declare @xml xml,
@max_len int

insert @t3 (id, data3)
select id, SubString(data,(LEN (data) - CharIndex (' ', REVERSE(data))+2),LEN (data)) as data2
from @t

set @xml =
(
select data3,
cast('' + replace(data3,'.','') + '' as xml)
from @t3
for xml path('id_root'),type
)

select @max_len = max(len(x.i.value('.','varchar(50)')))
from @xml.nodes('/id_root/i') x(i)

select T.data, T3.data3, srt.srtvalue
from @t3 T3
join @t T
ON T3.id = T.id
cross apply(
select
case
when ISNUMERIC(x.i.value('.','varchar(50)')) = 1
then right(replicate('0',@max_len) + x.i.value('.','varchar(50)'),@max_len)
else x.i.value('.','varchar(50)')
end + '.'
from @xml.nodes('/id_root/i') x(i)
where x.i.value('../data3[1]','varchar(50)') = [T3].data3
for xml path('')
) as srt(srtvalue)
order by SubString (data,1 ,CharIndex (' ', data)) ,srt.srtvalue

Result:
data
--------------------------------------------------
Area Lantai 1
Area 2
Rak 1
Rak 1.2
Rak 2
Rak 10
Rak 122

(7 row(s) affected)

Sort Varchar (eq 1.1)

declare @temp table (id varchar(255))

insert into @temp (id) values
('1.1.a.1'),('1.1.aa.2'),
('1.1.b.3'),('1.1.a.4'),
('1.1.a.5'),('1.1.a.6'),
('1.1.a.7'),('1.1.a.8'),
('1.1.a.9'),('21.1.a.10'),
('1.1.a.11'),('1.1.b.1'),
('2.1.b.2'),('1.2.a.1'),
('1.10.a.1'),('1.11.a.1'),
('1.20.a.1'),('101.20.a.2'),
('1.20.a.150'),('1.1'),
('1.2'),('1')

declare @xml xml,
@max_len int

select id as id, cast('' + replace(id,'.','') + '' as xml)
from @temp
for xml path('id_root'),type
set @xml =
(
select id as id, cast('' + replace(id,'.','') + '' as xml)
from @temp
for xml path('id_root'),type
)

select @max_len = max(len(x.i.value('.','varchar(10)')))
from @xml.nodes('/id_root/i') x(i)

select @max_len

select [id], srt.srtvalue
from @temp
cross apply(
select case when ISNUMERIC(x.i.value('.','varchar(10)')) = 1 then right(replicate('0',@max_len) + x.i.value('.','varchar(10)'),@max_len) else x.i.value('.','varchar(10)') end + '.'
from @xml.nodes('/id_root/i') x(i)
where x.i.value('../id[1]','varchar(50)') = [@temp].id
for xml path('')
) as srt(srtvalue)
order by srt.srtvalue

Result:
id
-------------------------
1
1.1
1.1.a.1
1.1.a.4
1.1.a.5
1.1.a.6
1.1.a.7
1.1.a.8
1.1.a.9
1.1.a.11
1.1.aa.2
1.1.b.1
1.1.b.3
1.2
1.2.a.1
1.10.a.1
1.11.a.1
1.20.a.1
1.20.a.150
2.1.b.2
21.1.a.10
101.20.a.2

(22 row(s) affected)

Sort VARCHAR on VARCHAR coloum (varchar + int)

SET NOCOUNT ON

Declare @Table table
(
Id INT Identity (1, 1),
StringValue VarChar (30)
)

INSERT INTO @Table (StringValue) VALUES ('CAR 10')
INSERT INTO @Table (StringValue) VALUES ('CAR 20')
INSERT INTO @Table (StringValue) VALUES ('CAR 2')
INSERT INTO @Table (StringValue) VALUES ('CAR 3')
INSERT INTO @Table (StringValue) VALUES ('CAR 4')

INSERT INTO @Table (StringValue) VALUES ('SHIP 32')
INSERT INTO @Table (StringValue) VALUES ('SHIP 310')
INSERT INTO @Table (StringValue) VALUES ('SHIP 320')
INSERT INTO @Table (StringValue) VALUES ('SHIP 33')
INSERT INTO @Table (StringValue) VALUES ('SHIP 34')

SELECT Id,
SubString (StringValue, 1, CharIndex (' ', StringValue)) ObjectName,
CONVERT (INT, SubString (StringValue, CharIndex (' ', StringValue), LEN (StringValue))) ObjectId
FROM @Table
ORDER BY 2, 3

SELECT Id, StringValue
FROM @Table
ORDER BY
SubString (StringValue, 1, CharIndex (' ', StringValue)),
CONVERT (INT, SubString (StringValue, CharIndex (' ', StringValue), LEN (StringValue)))

Thursday, 22 September 2011

Parse Coloumn into One Single String

There is 2 ways I know :
------------------------------------------------------------------------------------------
DECLARE @List VARCHAR(MAX)

SELECT
@List = ISNULL(EmployeeID + ', ' , '') + @List
FROM
Employee

SELECT @List
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------

SELECT
LEFT(CA.List, LEN(CA.List)-1)
FROM
(
SELECT
CONVERT(VARCHAR(100),EmployeeID) + ',' AS [text()]
FROM
Employee
FOR XML PATH('')
)CA(List)
-------------------------------------------------------------------------------------------

Sometimes way no 2 faster than no 1, but for some condition no 1 is faster.

Apply the APPLY Clause

The real magic happens when you use SQL Server 2005's new APPLY clause. The APPLY clause let's you join a table to a table-valued-function. That let's you write a query like this:

SELECT  C.CustomerID, 
	O.SalesOrderID,
	O.TotalDue
FROM 
	AdventureWorks.Sales.Customer AS C
CROSS APPLY
	AdventureWorks.dbo.fn_GetTopOrders(C.CustomerID, 3) AS O
ORDER BY 
	CustomerID ASC, TotalDue DESC

which results in this...

CustomerID  SalesOrderID TotalDue
----------- ------------ ---------------------
1           45283        37643.1378
1           46042        34722.9906
1           44501        26128.8674
2           46976        10184.0774
2           47997        5469.5941
2           57044        4537.8484
3           53616        92196.9738
3           47439        78578.9054
3           48378        56574.3871
4           47658        132199.8023
. . .

credit : http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

Friday, 16 September 2011

Javascript get text from dropdownlist

var ddlReport = document.getElementById("<%=DropDownListReports.ClientID%>");
var Text = ddlReport.options[ddlReport.selectedIndex].text;
var Value = ddlReport.options[ddlReport.selectedIndex].value;