Thanks Paul,

Its working Good one

but for some procedure its showing invalid character length and i observed that there is not purpose line in that i need to return...

You want to know how to write trigger or what ?

Please be specific

What assistance you want ?

Thank you all very much for your replies and efforts.

Paul i m sorry but your solution is working fine with the string what i have given to you but in...

Paul White NZ (4/5/2010)

---

vaibhav.tiwari (4/5/2010)

---

...but how can i replace @string with my column name...as i have to use it with the table otherwise i will have to use it by...

Paul White NZ (4/5/2010)

---

Updated implementation:

DECLARE @String NVARCHAR(MAX);

SET @String = N'======Heading========== some phrase ===============bottom line==============';

SELECT extract =

SUBSTRING(data, Start.pos,...

Actually my full string is

'CREATE PROCEDURE [dbo].[test] @INID BIGINT /*-------------------------------------------------------------------- DECLARE @b-2 INT EXEC test 1 ====================PURPOSE=========================================== Delete ID...

DateTime2 is not the datatype in sql 2005. it is the datatype of sql server 2008.

See BOL for more help on new datatypes for dates.

Paul White NZ (4/2/2010)

---

vaibhav.tiwari (4/2/2010)

---

Now what should i do for null values in result set as those are also dates not the garbage data.and i have to do some date...

Paul White NZ (4/2/2010)

---

vaibhav.tiwari (4/2/2010)

---

I was asking that if i will use your query then it will give me null for all invalid date but that might be possible that...

Lynn Pettis (4/2/2010)

---

Another issue you run into is this:

02-05-2010 is this February 5, 2010 or May 2, 2010?

Do you have a way to determine which is the correct interpretation of...

Thanks for the reply

but...

lmu92 (4/2/2010)

---

Something like this?

CREATE TABLE #TableA ( id INT,columnA VARCHAR(200))

INSERT INTO #TableA

SELECT 1,'here is my phone number 12345678. but my cell is 44444444.' UNION ALL

SELECT 2,'987654321 is my balance' UNION...

Hi Lynn

i have 38105166 data in one table. if i can get all the different format which are being used in that column then i can create a query...

Paul White NZ (4/1/2010)

---

Glad you used the word 'garbage' rather than...