WHats wrong


Anyone who knows a smarter way to select a special part of a text string.

A have done like this now but mabye the textstring changes. I know that the GO02 in the future will expand.

KnowgoodP_GO02_AA_K_20070807_2010_L.BBB

But the underscore _ sign is like a seperator and will always be there to separate the words.

I need the 20070807_2010 and fomat to 2007-08-07 20:10

SELECT @state = STATE,
@thetime = CONVERT(char(20),STATE_TIME,20),
@id = ID,
@textstrr = TEXTSTRR

WHERE SUBSTRING(FILENAME, 21,4)+ '-'+ SUBSTRING(FILENAME, 25,2)+'-'+SUBSTRING(FILENAME, 27,2)+' '+ SUBSTRING(FILENAME, 30,2)+ ':'+ SUBSTRING(FILENAME, 32,2)+':00' between @starttime and @theEndTime



Re: SELECT character in a string (masking)

Jens K. Suessmeyer


YOu can use my split function with the _ as the field value delimiter:

http://forums.microsoft.com/TechNet/ShowPost.aspx PostID=419984&SiteID=17

Jens K. Suessmeyer

---
http://www.sqlserver2005.de
---






Re: SELECT character in a string (masking)

oj

You can do this.

e.g.

--replace _ for space(1)

--convert string to datetime

--extract first 16 chars

select convert(char(16),convert(datetime,replace('20070807_20:10','_',' ')),120)







Re: SELECT character in a string (masking)

Marreoragarn

I mixed my SPTongue Tied

The question should be like this. the criteria is TEXTSTRR Not FILENAME

SELECT @state = STATE,
@thetime = CONVERT(char(20),STATE_TIME,20),
@id = ID,
@textstrr = TEXTSTRR

WHERE SUBSTRING(TEXTSTRR, 21,4)+ '-'+ SUBSTRING(TEXTSTRR, 25,2)+'-'+SUBSTRING(TEXTSTRR, 27,2)+' '+ SUBSTRING(TEXTSTRR, 30,2)+ ':'+ SUBSTRING(TEXTSTRR, 32,2)+':00' between @starttime and @theEndTime

A have done like this now but mabye the textstring changes. I know that the GO02 in the future will expand.

KnowgoodP_GO02_AA_K_20070807_2010_L.BBB

But the underscore _ sign is like a seperator and will always be there to separate the words.

I need the 20070807_2010 and fomat to 2007-08-07 20:10

oj!

what does this do

--replace _ for space(1)





Re: SELECT character in a string (masking)

Jens K. Suessmeyer

Usign the split function you could easily try to determine the date value and the time:

SELECT

ISDATE(SplitValue),

SplitValue

IsDateValue

FROM dbo.Split('KnowgoodP_GO02_AA_K_20070807_2010_L.BBB','_')



Jens K. Suessmeyer

---
http://www.sqlserver2005.de
---





Re: SELECT character in a string (masking)

Marreoragarn

Thanks!

Do you think this way will be faster than the way I masked it





Re: SELECT character in a string (masking)

Marreoragarn

How to find the _-sign or the .- sign in a textstring

If I search for the 4:e _-sign I can easy find the date and time and also the last sign in the string. Because I also need the last sign.

That will help me very much.





Re: SELECT character in a string (masking)

oj

I think I see what you're trying to accomplish. Try this:

Code Snippet

SELECT @state = STATE,
@thetime = CONVERT(char(20),STATE_TIME,20),
@id = ID,
@textstrr = TEXTSTRR

WHERE

replace(TEXTSTRR,left(right(TEXTSTRR,19),13),
convert(char(16),convert(datetime,stuff(replace(left(right(TEXTSTRR,19),13),'_',' '),11,0,':')),120))

between @starttime and @theEndTime






Re: SELECT character in a string (masking)

Marreoragarn

Thanks!

It works fine!