replacing integer values by date in a text field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

replacing integer values by date in a text field

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2009-05-26 : 11:04:20

Hi,
I have a string as:


declare @str_ori varchar (4000)

set @str_ori = '1224237533dat\peoplethis is the first line statement. 1234869276dat\peoplethis is the second line statement...'

the integers are basically date values which I need to convert to date time and then again show the text as follows:


2009-04-29 00:00:00.000dat\peoplethis is the first line statement. 2009-05-29 00:00:00.000dat\peoplethis is the second line statement...

can any body plz. suggest me if this is possible using T-SQL?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-26 : 11:10:41

yes. this is possible. You can use replace() to do it. But how do you convert the numbers to datetime ?

KH
[spoiler]Time is always against us[/spoiler]

zion99
Posting Yak Master

141 Posts

Posted - 2009-05-26 : 11:13:10

after I identify a particular integer group, i will be using the foll. function:


DATEADD(ss, FOUND_INTEGER, '01/01/1970 00:00:00 AM')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 14:34:26

this might give you a start

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx

zion99
Posting Yak Master

141 Posts

Posted - 2009-05-27 : 09:48:29

thanks visakh16, unfortunately that query joins all the numbers.

i did some changes, as a result, i get a different result. But even then i dont think i can get separated numbers from the current string.


Declare @s varchar(100),@result varchar(100),@CountNum int
set @CountNum = 0
set @s='1224237533dat\peoplethis is the first line statement. 1234869276dat\peoplethis is the second line statement...'
set @result=''

select
    @result=@result+case when number like '[0-9]' then number else '' end  from 
    (
         select substring(@s,number,1) as number from 
        (
            select number from master..spt_values where type='p' and number between 1 and len(@s)
        ) as t
    ) as t 
select @result as only_numbers --, @CountNum as [Total numbers]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources