need to extract text from given string

Question

need to extract text from given string

komal145

SSCrazy Eights

Points: 9918
More actions
September 19, 2016 at 9:30 am

#400740

Given string I need to get two values :
example 1 string : Day20ReminderCampaign2016_09_15_13:46:57
needed :
1) Day20ReminderCampaign
2 ) 20160915 13:46:57
example 2 string Day3ReminderCampaign2016_09_19_02:28:39
needed :
1) Day3ReminderCampaign
2 ) 20160919 02:28:39

Viewing 7 posts - 1 through 6 (of 6 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Use PATINDEX to find where the text finishes and the date starts you can use '20[0-9][0-9]' or something like that as your pattern match. Once you've got that, it's easy to use LEFT, RIGHT and LEN to split the two components out.

John

Thom A SSC Guru Points: 99133 More actions · Answer 2

Looking at your two examples, can I assume that the date at the end is always in yyyy_MM_dd_HH:mm:ss format? If so, this would work:

Create table #examples (string varchar(100));

Insert into #examples

Values ('Day20ReminderCampaign2016_09_15_13:46:57'),

('Day3ReminderCampaign2016_09_19_02:28:39');

Select e.string,

LEFT(e.string, len(e.string) - 19) as CampaignString,

replace(LEFT(right(e.string, 19), 10),'_','') + ' '+ RIGHT(e.string, 8) as DatetimeString

from #examples e;

Drop table #examples;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

komal145 (9/19/2016)
Given string I need to get two values :
example 1 string : Day20ReminderCampaign2016_09_15_13:46:57
needed :
1) Day20ReminderCampaign
2 ) 20160915 13:46:57
example 2 string Day3ReminderCampaign2016_09_19_02:28:39
needed :
1) Day3ReminderCampaign
2 ) 20160919 02:28:39

Please provide what rules you have (if any) about what those two sets of strings can look like. Easiest by far would be if either the text or the date are always EXACTLY the same length. For example, if ALL of the date/time values look EXACTLY like that (including leading zeros). then it is trivial:

select right(field,19) as datetimeraw, left(field,len(field)-19) as fieldtext

You may need to adjust the LEFT value by one.

I leave it to you to take datetimeraw and manipulate it to a valid datetime string. 😎

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

You need to get in the habit of posting information in a consumable format.

create table #Something

(

SomeValue varchar(100)

)

insert #Something

select 'Day20ReminderCampaign2016_09_15_13:46:57' union all

select 'Day3ReminderCampaign2016_09_19_02:28:39'

From there is it is fairly trivial to leverage some string functions to do this. Here is one way.

select LEFT(SomeValue, CHARINDEX('_', SomeValue) - 5)

, STUFF(

replace(SUBSTRING(SomeValue, CHARINDEX('_', SomeValue) - 4, LEN(SomeValue)), '_', '-')

, CHARINDEX(':', replace(SUBSTRING(SomeValue, CHARINDEX('_', SomeValue) - 1, LEN(SomeValue)), '_', '-'), 1)

, 1

, ' ')

from #Something

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

My solution:

-- sample data

DECLARE @sampleData TABLE (stringID int, string varchar(1000));

INSERT @sampleData

VALUES

(1, 'Day20ReminderCampaign2016_09_15_13:46:57'),

(2, 'Day3ReminderCampaign2016_09_19_02:28:39');

--Solution

SELECT

stringID,

part1 = SUBSTRING(string,1,split-1),

part2 = STUFF(REPLACE(SUBSTRING(string,split,8000),'_',''),9,0,' ')

FROM

(

SELECT *, split = PATINDEX('%[0-9][0-9][0-9][0-9][_][0-9][0-9]%',string)

FROM @sampleData

) Prep;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

komal145 SSCrazy Eights Points: 9918 More actions · Answer 6

Thanks to everyone. Your responses are helpful , it works as needed.