Help with weird Select statement

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)

Help with weird Select statement

Author

Topic

transcom_bcn
Starting Member

16 Posts

Posted - 2007-04-11 : 11:41:02

- Hi, I'm facing the following situation:

I have in a table, a text field looks like this:

... (a variable set of characters where the comma is *not* included) , (a comma) ... (a variable set of characters, where the comma is included)

Well, I need to obtain from each record, the 17 characters *after the first comma* (a datetime value)

E.g.:

blah blah blah, 2007-04-11 17:00 blah blah blah, blah blah blah

I know that it should be something simpler that I think, but I've been working on this and I haven't arrived to a solution that works for all the possible cases (that table has millions of records).

Help on this would be very welcome.

Regards,
Alejandro
Barcelona, Spain

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-11 : 11:50:15

[code]
declare @str varchar(100)
select @str = 'blah blah blah, 2007-04-11 17:00 blah blah blah, blah blah blah'

select ltrim(substring(@str, charindex(',', @str) + 1, 17))
[/code]

transcom_bcn
Starting Member

16 Posts

Posted - 2007-04-13 : 05:15:55

quote:

Originally posted by khtan


declare @str varchar(100)
select @str = 'blah blah blah, 2007-04-11 17:00 blah blah blah, blah blah blah'

select	ltrim(substring(@str, charindex(',', @str) + 1, 17))

Hi khtan, thanks for your help, it works fine now.

Regards,
Alejandro
Barcelona, Spain

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-13 : 06:47:10

quote:

Originally posted by khtan


declare @str varchar(100)
select @str = 'blah blah blah, 2007-04-11 17:00 blah blah blah, blah blah blah'

select	ltrim(substring(@str, charindex(',', @str) + 1, 17))

Why did you use ltrim?
Didnt you consider space as Character?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-13 : 09:46:04

Yup


declare @str varchar(100)
select  @str = 'blah blah blah, 2007-04-11 17:00 blah blah blah, blah blah blah'

select	[with ltrim] 	= '[' + ltrim(substring(@str, charindex(',', @str) + 1, 17)) + ']',
	[without ltrim]	= '[' +      (substring(@str, charindex(',', @str) + 1, 17)) + ']'
/*
with ltrim                           without ltrim                        
------------------------------------ ------------------------------------ 
[2007-04-11 17:00]                   [ 2007-04-11 17:00]

(1 row(s) affected)
*/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-13 : 12:43:37

Well. Thanks

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources