spliting the data using replace function

Question

Post reply

spliting the data using replace function

anitha.cherukuri

SSCommitted

Points: 1514
More actions
December 9, 2009 at 6:40 pm

#214659

Hi friends
I have one coulmn in my table ,it has data look like this..
Number=122time=1209/n/n/nonsite=1240/n/n
when i create the report ,the colmn is displaying like this
Number=122
time=1209
nonsite=1240
what i want here i want to display the above text as 3 columns ,
any help would be appreiciared...
tahnks,,,

Viewing 15 posts - 1 through 15 (of 26 total)

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

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

Divide'n'Conquer...

DECLARE @TestString VARCHAR(100);

SELECT @TestString = 'Number=122time=1209/n/n/nonsite=1240/n/n';

WITH

cteStart AS

(

SELECT SUBSTRING(@TestString,CHARINDEX('Number=' ,@TestString)+7,100) AS NumberStart,

SUBSTRING(@TestString,CHARINDEX('time=' ,@TestString)+5,100) AS TimeStart,

SUBSTRING(@TestString,CHARINDEX('nonsite=',@TestString)+8,100) AS NonSiteStart

)

SELECT SUBSTRING(NumberStart ,1,CHARINDEX('time=',NumberStart )-1) AS Number,

SUBSTRING(TimeStart ,1,CHARINDEX('/n' ,TimeStart )-1) AS Time,

SUBSTRING(NonSiteStart,1,CHARINDEX('/n' ,NonSiteStart)-1) AS NotSite

FROM cteStart;

--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)

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 2

anitha.cherukuri

SSCommitted

Points: 1514

December 10, 2009 at 12:13 am

#1090371

Thanks jeff,i really appreciate your help,,,,,,,,,,,

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

You bet, Anitha. Thank you for the feedback.

--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)

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 4

Hi jeff

Sorry for giving trouble to you,,,,,,

i am in trouble in witing sql for below problem

My column has data like this

Number:122

time:1209

nonsite:(menas null)

verbal:out control

What i want

Number time nonsite verbal

122 1209 out conrol

Can you please help me

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 5

Plaese ask me if you want more information,,,,

thanks ,,,

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 6

I won't be able to get to this until I get home from work. If you study the code I've offered so far, you'll probably figure it out for yourself in no time. If you can't, could you post the "flat line" that appears like you did in your original post? Thanks.

--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)

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 7

Hi Jeff

thanks for time.

First i used char(13)+Char(10) function to find out enter symbols(Carraige returns)

then my clomn is displaying like this...

test='GUARD NUMBER:NC122 TauZZDISPATCH TIME:1907ZZTIME ONSITE:1930ZZTIME OFFSITE:ZZOUTCOME:Verbal WarningZZ'

what i want here

guard number=nC122 Tau

Dispatch time=1970

time onsite=1930

time offsite=

outcome =verbal warning,

note :zz means i am displaying zz value as default when carriage return found in text.

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 8

anitha.cherukuri

SSCommitted

Points: 1514

December 10, 2009 at 5:05 pm

#1090928

thanks once again...

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 9

Ok... here you go. You need to study this, Anitha, because every string like this follows a similar pattern of FieldName:fieldvalue fieldterminator. You should be able to do this on your own whenever you run into something like this. Read the comments in the code below for what each piece of code means. You'll be an expert on this in no time. 😉 And, yes, with just a minor tweek here and there, you can do a whole table at once without using a UDF, cursor, while loop, recursion, or other form of RBAR. 😀

DECLARE @TestString VARCHAR(256),

@MaxLength INT;

SELECT @MaxLength = 256; --Should be same as the VARCHAR() definition of @TestString above or column in a table

SELECT --This just builds the test string and isn't a part of the actual solution.

@TestString = 'GUARD NUMBER:NC122 TauZZDISPATCH TIME:1907ZZTIME ONSITE:1930ZZTIME OFFSITE:ZZOUTCOME:Verbal WarningZZ',

@TestString = REPLACE(@TestString,'ZZ',CHAR(13)+CHAR(10)); --Replaces "ZZ" with a CrLf to simulate the real text.

WITH

cteStart AS

(

--Each field gets it's own SUBSTRING here. Finds the "start" of each field and remembers from there to the end.

--Put bogus field names over here >>>---------------------------------------------------------|

--Put length of field header found here >>>---| |

-- | ... over here >>>---------| |

-- (these are from the string) |-----------| | |

-- V V V V

SELECT SUBSTRING(@TestString, CHARINDEX('GUARD NUMBER:' ,@TestString) + 13, @MaxLength) AS F1Start,

SUBSTRING(@TestString, CHARINDEX('DISPATCH TIME:' ,@TestString) + 14, @MaxLength) AS F2Start,

SUBSTRING(@TestString, CHARINDEX('TIME ONSITE:' ,@TestString) + 12, @MaxLength) AS F3Start,

SUBSTRING(@TestString, CHARINDEX('TIME OFFSITE:' ,@TestString) + 13, @MaxLength) AS F4Start,

SUBSTRING(@TestString, CHARINDEX('OUTCOME:' ,@TestString) + 8, @MaxLength) AS F5Start

)

--Each field gets it's own SUBSTRING here, too. Finds the "end" of each field and returns substring for field.

--Put desired column name for each field here >>>---------------------|

--Will always be -1 here >>>---------------------------------| |

--Bogus field name from the CTE above >>>------------| | |

--Whatever the field ends with >>>---------| | | |

--Always "1" >>>-----------| | | | |

--Bogus field name --| | | | | |

-- V V V V V V

SELECT SUBSTRING(F1Start ,1, CHARINDEX(CHAR(13), F1Start ) -1) AS Guard_Number,

SUBSTRING(F2Start ,1, CHARINDEX(CHAR(13), F2Start ) -1) AS Dispatch_Time,

SUBSTRING(F3Start ,1, CHARINDEX(CHAR(13), F3Start ) -1) AS Time_Onsite,

SUBSTRING(F4Start ,1, CHARINDEX(CHAR(13), F4Start ) -1) AS Time_OffSite,

SUBSTRING(F5Start ,1, CHARINDEX(CHAR(13), F5Start ) -1) AS Outcome

FROM cteStart;

We could write some code to automatically discover the field names and have the program actually write the code above, but you have to learn how to do it manually first so you can troubleshoot automatic code.

--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)

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 10

I really thankfull to you,you are so wondefull .........

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 11

Thanks, Anitha :blush:

--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)

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 12

anitha.cherukuri

SSCommitted

Points: 1514

December 10, 2009 at 6:42 pm

#1090943

thanks jeff

you are wonderfull ,

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 13

Hi jeff

i am very to sorry to disturb you,

your query is awesome,

but i have to include your bit in my select statement.

here is the code..

SELECT

document.RegdAt AS [Date Logged],

document.DocNo AS [Request No],

Action.NoteText AS NoteTEXT---------------here i have to use your bit to display different columns

FROM dbo.Document

INNER JOIN Action on Action.DocNo = document.DocNo

AND (document.RegdAt BETWEEN @DTStart AND @DTEnd)

ORDER BY document.DocNo

please let me know if you want any information

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 14

Save the output of your query into a table with the extra columns you need. Then, use my query to UPDATE the table.

--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)