Database

SELECT * FROM [Table] WHERE [Field] LIKE '%[%]'

thatraja |Chennai|India|

---

Tips/Tricks|Brainbench certifications

Do what you want quickly because the Doomsday on 2012

eddieangel wrote:

There will always be a 1:1 relationship between the two tables.

I are Troll

eddieangel wrote:

There will always be a 1:1 relationship

Stryder_1 wrote:

I want the DataField value to be unique only per client

Client
ID
Name
DataField

File
ID
Name
Bytes
ClientID

Client
ID
Name

File
ID
Name
Bytes
DataField
ClientID

I are Troll

modified on Monday, October 18, 2010 3:56 PM

CREATE FUNCTION [dbo].[Split]
(
  @delimited nvarchar(max),
  @delimiter nvarchar(100)
) RETURNS @t TABLE
(
  id int identity(1,1), 
  val nvarchar(max)
)
AS
BEGIN
  declare @xml xml
  set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'

  insert into @t(val)
  select 
    r.value('.','varchar(15)') as item
  from @xml.nodes('//root/r') as records(r)

  RETURN
END

declare @tmp table (data varchar(50))
insert into @tmp (data) values ('MKG Ex Du A918731765L')
insert into @tmp (data) values ('TVN Ex Du A919721354L')
insert into @tmp (data) values ('NCL Ex Du A901713191L')
insert into @tmp (data) values ('Bin Ex Du A948606458L')

select [1] as 'SK1', [2] as 'SK2', [3] as 'SK3', substring([4], 0, 10) as 'SK3', substring([4], 10, 2) as 'SK4'
from
(
  select [1] , [2] , [3] , [4]
  from (
    select * from
    @tmp t
    cross apply dbo.Split(t.data,' ')
  ) as sourcetb
  pivot
  (max(val) for id in ([1],[2],[3],[4])
  ) as pivottable
) as t

SELECT FID,Code,Max(Value1),Max(Value2)
FROM TableName
Group By FID,Code