please see my response to your other question

http://www.sqlservercentral.com/Forums/Topic1353196-2799-1.aspx

Hello and welcome to the...

a variation....

DECLARE@NumberTable TABLE( ItemNumber VARCHAR(50))

INSERT@NumberTable

SELECT'ABC-1702-XYZ' ItemNumber UNION ALL

SELECT'ABC-1727-XYZ'UNION ALL

SELECT'ABC-1729-XYZ'UNION ALL

SELECT'ABC-895-XYZ'UNION ALL

SELECT'ABC-2119-XYZ'UNION ALL

SELECT'ABC-1746-XYZqqqq'UNION ALL

SELECT'ABC-900-dfdfdfXYZ'UNION ALL

SELECT'ABCasasa-2215-XYZabc'UNION ALL

SELECT'ABC-64533333-XYZ'UNION ALL

SELECT'ABC121-1783-333XYZ'UNION ALL

SELECT'ABC-2193-XYZ'UNION ALL

SELECT'ABC-830-XYZ'UNION ALL

SELECT'ABC-646-XYZ'UNION ALL

SELECT'ABC-796-XYZ'

select *

from @NumberTable

order by cast (SUBSTRING(

ItemNumber,...

mfundotogu (8/26/2012)

---

@

I ddnt knw posting the same post many time is spamming ,I was trying to get help as quick as possible. The project tht I'm doing is industrial not...

to try and help out...I'll throw the following code into the conversation

it may or may not be appropriate...but hopefully OP will reply with some code that helps us understand.

USE [TEMPDB]...

whar results are supposed to be returned for this data set?

CREATE TABLE #Test

(ID int NOT NULL IDENTITY(1,1) Primary key,

TranID int NOT NULL,

OriginCode varchar(5) NOT NULL,

DestinationCode varchar(5) NOT NULL

...

any changes to the way you connect to Oracle...eg linked server odbc drivers / 32/64bit etc?

???

sorry...but dont understand your comment.

Cadavre (8/20/2012)

---

I think that this improves your code slightly: -

WITH CTE AS (SELECT DISTINCT TranID,

CASE WHEN...

sql-lover (8/20/2012)

---

MysteryJimbo (8/19/2012)

---

Partitioning is not really a solution to performance, there are performance problems with aggregations etc. If they clarify they want x years of data then you should...

does this work for you?

;with cte as

(

SELECT ID, TranID, OriginCode AS scode, 1 as col

FROM #Test

union all

SELECT ID, tranID, DestinationCode AS scode, 2 as col

FROM #Test

),

cte2 as

(

select...

something like this maybe?

SELECT

tranid,

(

SELECT OriginCode + DestinationCode

FROM #test p2

WHERE p1.tranid = p2.tranid

...

vivekkumar341 (8/19/2012)

---

declare

@text varchar(max) = 'MSH|^~\&|||||21110428134755-0500||ADT^A04^ADT_A01|21110428134755|P|2.5.1|||AL|NE

EVN|A08|21110428133821-0500||||21110428133821-0500

PID|zdly100001|zdly111000^556|PATID1234^5^M11|6g7g7|JONES^WILLIAM^Aryan^III^Dr.^MBA|Singh|19621225000000|M||2106-3|1200 N ELM STREET^^GREENSBORO^NC^27401-1020^TX|GL|(919)379-1212^^^test1@gmail.com^TX^324^5656568^121|(919)271-3434~(919)277-3114||D^|CHR^|PATID12345001^2^M10|66666611111|9-87654^NC^20121217||H|||||||20181220225700

NK1|1||EMR^Employer^HL71113|9900 Spectrum Drive^^Austin^TX^78717^^B||(512)257-5200^WPN^^^^512^2575200|E^Employer^HL71111|21110813||Support Representative||87|e-MDs, Inc.|| '

Suppose above is the string where i want to fetch the value

"WILLIAM"

which is present In "PID" segment line

how...

hmmmm...not really the best response for someone who has just...

sql-lover (8/18/2012)

---

-Performance

-Disk's space (the whole db is over 1TB now)

Reducing the table will allow me to save lot of space and RAM. The reports that take almost an hour, will...