December 11, 2009 at 3:12 am
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.
December 13, 2009 at 12:18 pm
thanks jeff,,,,,,
December 13, 2009 at 1:49 pm
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" >>>-----------| | | | |
--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,
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
December 13, 2009 at 1:54 pm
Hi Jeff
I solved my problem by using temp table concept.
sorry for giving trouble....
December 13, 2009 at 5:19 pm
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
December 13, 2009 at 5:22 pm
anitha.cherukuri (12/13/2009)
Hi JeffI 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
December 13, 2009 at 5:23 pm
anitha.cherukuri (12/13/2009)
Hi JeffI 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
December 13, 2009 at 7:35 pm
Thanks jeff
i will do ,,
January 28, 2010 at 3:57 pm
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" >>>-----------| | | | |
--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,
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;
January 28, 2010 at 8:09 pm
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
January 29, 2010 at 5:22 pm
Thanks Jeff.It works awesome.
Kind Regards,
Anitha
January 29, 2010 at 5:45 pm
You bet. Thank you for the feedback.
--Jeff Moden
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply