Extract data from a string

Question

Extract data from a string

e90

SSC Journeyman

Points: 84
More actions
May 14, 2007 at 6:47 pm

#153586

Hi all,
If i have a record like this what is the best way(syntax) to extract data into several columns like
column 1 = Dtautas, column 2 = 2/22/2007 5:22:32 PM ?
i may have record with date time like PM: or AM: like:
'Dtautas2/22/2007 5:22:32 PM: text'
'Dtautas2/22/2007 5:22:32 AM: text'
The problem here the text format is not always the same for all records and for those with PM: or AM: i need to go backwords and extract data.
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

jezemine SSCrazy Eights Points: 8059 More actions · Answer 1

T-SQL is not the optimal language for string parsing. You might consider exporting the data to a flat file and then parsing it with a language built for the task, such as perl.

Then re-import the parsed data into columns with the proper datatypes, like datetime instead of varchar, etc.

---------------------------------------
elsasoft.org

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Or, if you know your data, you could build on this:

declare @varTable table (

TestData varchar(max)

)

insert into @varTable values ('Dtautas2/22/2007 5:22:32 PM: text')

insert into @varTable values ('Dtautas2/22/2007 5:22:32 AM: text')

insert into @varTable values ('Dtautas12/22/2007 5:22:32 AM: text')

select * from @varTable

select

substring(TestData, 1, patindex('%[0-9][0-9/]%', TestData) - 1),

substring(TestData, patindex('%[0-9][0-9/]%', TestData), patindex('%M:%', TestData) - (patindex('%[0-9][0-9/]%', TestData)) + 1),

substring(TestData, patindex('%M:%', TestData) + 2, len(TestData) - (patindex('%M:%', TestData)) + 1)

from

@varTable

e90 SSC Journeyman Points: 84 More actions · Answer 3

e90

SSC Journeyman

Points: 84

May 15, 2007 at 10:17 am

#706573

Thanks you so much.

e90 SSC Journeyman Points: 84 More actions · Answer 4

Another question:

If i want to find if the string has spaces between words how can i do it?

i tried to use charindex but it always returns 0 for this example:

'Cx fd del rge for LM'

Also if the the space is found then i want to assign NULL to the variable.

Thanks

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 5

use the CHARINDEX string function.

EXAMPLE:

BEGIN

declare @t_ table (col1_ varchar(20))

insert into @t_ values ('12345xxxx')

insert into @t_ values ('12345 xxxx')

insert into @t_ values ('1234 5xxxx')

insert into @t_ values ('12345yyyy')

select * from @t_

update @t_

set col1_ = (case when charindex(' ',col1_) > 0 then NULL else col1_ end)

select * from @t_

END

--Same thing outside of a select statement

BEGIN

declare @var1_ varchar(20)

declare @var2_ varchar(20)

set @var1_ = '2345'

set @var2_ = '23 45'

if charindex(' ',@var1_) > 0

set @var1_ = NULL

if charindex(' ',@var2_) > 0

set @var2_ = NULL

print @var1_

print coalesce(@var2_,'NULL')

END

e90 SSC Journeyman Points: 84 More actions · Answer 6

e90

SSC Journeyman

Points: 84

May 16, 2007 at 4:22 pm

#706956

Thanks so much

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

You can go a bit further... the following will return the number of spaces found for each row for that column...

SELECT pkcolumnname, LEN(yourcolumn) - LEN(REPLACE(yourcolumn,' ','')) AS Spaces

FROM yourtable

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

e90 SSC Journeyman Points: 84 More actions · Answer 8

i do have a date sometimes returns as a string as '24/26/2007 11:23:33 AM'

or '112/26/2007 11:23:33 AM'

What i need to do is to check for a month and if the month is greater than 12 then remove 1st digit from '24/26/2007 11:23:33 AM' or 1st digit from '112/26/2007 11:23:33 AM' to get a valid date.

Can i use datepart here somehow to get a month and then manupulate with a month part ?

Currently '24/26/2007 11:23:33 AM' defined as a varchar.

Thanks