spliting the data using replace function

Question

spliting the data using replace function

Viewing 12 posts - 16 through 26 (of 26 total)

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

ss-457805 SSCertifiable Points: 5872 More actions · Answer 1

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;

This is brilliant Jeff. Very Well explained. One for my Useful SQL script collection.

Thank you.

blog: http://sarveshsingh.com Twitter: @sarveshsing

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

anitha.cherukuri

SSCommitted

Points: 1514

December 13, 2009 at 12:18 pm

#1091694

thanks jeff,,,,,,

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

Hi Jeff

I am very new to functions concept ,and i can not create table to hold the results.

I wrote the function,but getting errors..

CREATE FUNCTION dbo.fn_nscc_SplitStringToCols

(

@TestString VARCHAR (700)

, @MaxLength INT

)

RETURNS @AddressSlabs TABLE (

Col1 VARCHAR (100) NULL

, Col2 VARCHAR (100) NULL

, Col3 VARCHAR (100) NULL

, Col4 VARCHAR (100) NULL

, Col5 VARCHAR (100) NULL

, Col6 VARCHAR (100) NULL

, Col7 VARCHAR (100) NULL

)

AS

BEGIN

-- declare vars

-- set defaults and initial var values

-- SELECT

-- @MaxLength = 256; -- default is max possible ie 100

INSERT INTO @AddressSlabs

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,

SUBSTRING(@TestString, CHARINDEX('END # :' ,@TestString) + 7, @MaxLength) AS F6Start,

SUBSTRING(@TestString, CHARINDEX('TIME END ISSUED:' ,@TestString) + 16, @MaxLength) AS F7Start

)

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

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

SUBSTRING(F6Start ,1, CHARINDEX(CHAR(13), F6Start ) -1) AS END#,

SUBSTRING(F6Start ,1, CHARINDEX(CHAR(13), F7Start ) -1) AS TIME_END

FROM cteStart;

RETURN

END

Can you plaese help me that how can i solve this problem

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

Hi Jeff

I solved my problem by using temp table concept.

sorry for giving trouble....

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

sav-457805 (12/11/2009)
This is brilliant Jeff. Very Well explained. One for my Useful SQL script collection.

Thank you, Sav... the feedback is always appreciated. Glad I could help.

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

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

anitha.cherukuri (12/13/2009)
Hi Jeff
I am very new to functions concept ,and i can not create table to hold the results.
I wrote the function,but getting errors...

Writing a function for this will just slow things down unless it's an inline table valued function. In this case, that would serve no purpose.

Using the methods found in the article at the first link in my signature below, provide 10 lines of data in the format stipulated in that article and I'll show you how to do this.

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

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

anitha.cherukuri (12/13/2009)
Hi Jeff
I solved my problem by using temp table concept.
sorry for giving trouble....

Sorry... didn't see this before I posted my last message. Two way street here, Anitha... please post your solution so we can all benefit. 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 8

anitha.cherukuri

SSCommitted

Points: 1514

December 13, 2009 at 7:35 pm

#1091737

Thanks jeff

i will do ,,

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

Hi Jeff

The SQL code what you provided worked well.

Unfotunately If notetext does not have searching strings ,its giving error.

Here is the example,Can I know how to solve this issue.

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 = 'zz',

@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,

SUBSTRING(@TestString, CHARINDEX('END # :' ,@TestString) + 7, @MaxLength) AS F6Start,

SUBSTRING(@TestString, CHARINDEX('TIME END ISSUED:',@TestString) + 16, @MaxLength) AS F7Start,

DocumentView.DocNo FROM dbo.DocumentView

INNER JOIN dbo.ActionView on ActionView.DocNo = DocumentView.DocNo

INNER JOIN dbo.PropertyView on PropertyView.DocNo = DocumentView.DocNo

INNER JOIN NotesView AS NV ON DocumentView.DocNo = NV.DocNo

WHERE (ActionView.I98L1ID = 24)

AND (ActionView.I98L2ID IN (466, 481, 482, 483, 484))

AND NV.NoteTitle = 'ICON site attendance'

AND (DocumentView.RegdAt BETWEEN '2010-01-17' AND '2010-01-24')

and NoteText IS NOT NULL

)

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

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

SUBSTRING(F6Start ,1, CHARINDEX(CHAR(13), F6Start ) -1) AS EndDate,

SUBSTRING(F7Start ,1, CHARINDEX(CHAR(13), F7Start ) -1) AS Time_End_Issued,DocNo

FROM cteStart;

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

anitha.cherukuri (1/28/2010)
Unfotunately If notetext does not have searching strings ,its giving error.

I was under the impression that all of the "searching strings" would be available for every row. Since you say they are not, then you need to use a CASE statement to test for each search string. For example...

... replace this...

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

... with this...

CASE

WHEN CHARINDEX('GUARD NUMBER:' ,@TestString) > 0

THEN SUBSTRING(@TestString, CHARINDEX('GUARD NUMBER:' ,@TestString) + 13, @MaxLength)

ELSE ''

END AS F1Start,

Do a similar thing to the other "fields" in the CTE.

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

anitha.cherukuri

SSCommitted

Points: 1514

January 29, 2010 at 5:22 pm

#1111076

Thanks Jeff.It works awesome.

Kind Regards,

Anitha

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

You bet. 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)