November 10, 2009 at 7:12 am
Hello,
I am trying to pull data out of a Varchar field that is surrounded by Chr(13). This data can sometimes have a period or comma in it so using something like PARSENAME(REPLACE(defendant_info,CHAR(13)+Char(10),'.'),2) AS defAdd will not work. Any ideas on how to pull that data using another method?
Thanks,
George
November 10, 2009 at 7:52 am
Can you post some sample data? See the top link in my signature for suggestions on how.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 10, 2009 at 8:00 am
Jack Corbett (11/10/2009)
Can you post some sample data? See the top link in my signature for suggestions on how.
Please note that the first two lines have a HARD RETURN after them and that is where I need it separated.
defendant_info
---------------
RIGDON MILLER & CO REAL ESTATE
1936 SPRUCE ST
19103
BRUNSON, MIRIAM
7238 SANSOM ST
19082
SMITH, JAMES J
1600 JACKSON ST
19145
MERTLE, JESSICA
205-15 ROCK ST
19128
JOHNSON, KALI
9815 HALDENMAN AVE
19115
PERSAND, GRACE
333 W ABBOTSFORD RD
19144
LAW, ANTHONY
333 W ABBOTSFORD RD
19144
My code so far looks as such and functions correctly but I cannot get the add field pulled out and inserted into defAdd. Also I will have to do the same thing with plaintiff_info but that data may or may not have 2 hard returns. It could have none, 1 or 2.
SELECT RecID, f_name, m_initial, l_name, combined_name, Court, plaintiff_info, JDate, defendant_info, revived_date, JAmt, DSN, CaseID, DocketTypeCode, jh_id, tdef_id, biannual_mainframe_id, excluded,
LEFT (defendant_info, CASE WHEN CHARINDEX(CHAR(13),defendant_info)>0 THEN CHARINDEX(CHAR(13),defendant_info) -1 ELSE LEN(defendant_info) END) AS def, defAdd, RIGHT (defendant_info, 5) AS defZip,
LEFT (defendant_info, CASE WHEN CHARINDEX(CHAR(13),plaintiff_info)>0 THEN CHARINDEX(CHAR(13),plaintiff_info) -1 ELSE LEN(plaintiff_info) END) AS tiff, tiffAdd,RIGHT (plaintiff_info, 5) AS tiffZip INTO allfixed
FROM copyofdefendantsAll
Thank you,
George
November 10, 2009 at 9:33 am
Where does a row stop in your example data?
How do you want the data returned?
It looks like you could do a "simple" split to get it in rows and then a pivot if you wanted something like:
defendent_name address postal_code
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 10, 2009 at 9:42 am
Jack Corbett (11/10/2009)
Where does a row stop in your example data?How do you want the data returned?
It looks like you could do a "simple" split to get it in rows and then a pivot if you wanted something like:
defendent_name address postal_code
The row is SQL looks like -- George Greiner CHR(13)CHR(10) 1234 Market Street CHR(13)CHR(10) Philadelphia, PA 19108
What I want to do is in a make table query drop the street address into defAdd. So in my table it would look like def, defAdd, defZip (not worried about city state in this one). In plaintiff_info it would look like tiff, tiffAdd, tiffCity, tiffState, tiffZip. The difference being we would have to notify the plaintiff so we need the full address and many times they do not have zip codes but rather just city / state but the defendant always has a zip code.
I wrote a query in Access to do this but it is too slow and quite cumbersome for my taste and hence why I am trying to do it in t SQL.
I actually tried using a split and a pivot but it did not work and I did not know enough about it to make adjustments to figure it out. It would not work if there were not ALWAYS 2 hard returns and it gives me a lot of nulls and I wanted to try something that I could understand better as my background is Access and the SQL used in Access.
November 10, 2009 at 10:20 am
george here's one way to do it;
i'm using a function CHARINDEX2, which finds the Nth occurrance of a string within the string.
with that, i can find the first CrLf and the second CrLf, and STUFF what's between out of existance.
here's a working example:
/*
Example:
SELECT dbo.CHARINDEX2('a', 'abbabba', 3)
returns the location of the third occurrence of 'a'
which is 7
*/
CREATE FUNCTION CHARINDEX2
(
@TargetStr varchar(8000),
@SearchedStr varchar(8000),
@Occurrence int
)
RETURNS int
as
begin
declare @pos int, @counter int, @ret int
set @pos = CHARINDEX(@TargetStr, @SearchedStr)
set @counter = 1
if @Occurrence = 1 set @ret = @pos
else
begin
while (@counter < @Occurrence)
begin
select @ret = CHARINDEX(@TargetStr, @SearchedStr, @pos + 1)
set @counter = @counter + 1
set @pos = @ret
end
end
RETURN(@ret)
end
GO
Create Table #Example(LongAddress varchar(500) )
INSERT INTO #Example
SELECT 'RIGDON MILLER & CO REAL ESTATE' + CHAR(13) + CHAR(10) + '1936 SPRUCE ST' + CHAR(13) + CHAR(10) + '19103' UNION ALL
SELECT 'BRUNSON, MIRIAM' + CHAR(13) + CHAR(10) + '7238 SANSOM ST' + CHAR(13) + CHAR(10) + '19082' UNION ALL
SELECT 'SMITH, JAMES J' + CHAR(13) + CHAR(10) + '1600 JACKSON ST' + CHAR(13) + CHAR(10) + '19145' UNION ALL
SELECT 'MERTLE, JESSICA' + CHAR(13) + CHAR(10) + '205-15 ROCK ST' + CHAR(13) + CHAR(10) + '19128' UNION ALL
SELECT 'JOHNSON, KALI' + CHAR(13) + CHAR(10) + '9815 HALDENMAN AVE' + CHAR(13) + CHAR(10) + '19115' UNION ALL
SELECT 'PERSAND, GRACE' + CHAR(13) + CHAR(10) + '333 W ABBOTSFORD RD' + CHAR(13) + CHAR(10) + '19144' UNION ALL
SELECT 'LAW, ANTHONY' + CHAR(13) + CHAR(10) + '333 W ABBOTSFORD RD' + CHAR(13) + CHAR(10) + '19144'
SELECT STUFF(LongAddress,--field to fiddle with
--where to start: first occurance of CrLf
dbo.CHARINDEX2(CHAR(13) + CHAR(10),LongAddress,1),
--how many chars, the diff betweent he second and the first
dbo.CHARINDEX2(CHAR(13) + CHAR(10),LongAddress,2) - dbo.CHARINDEX2(CHAR(13) + CHAR(10),LongAddress,1),
--replace with empty string
'')
FROM #Example
Lowell
November 10, 2009 at 10:21 am
I think this code does something similar to what you want. It is a split then pivot which you said you tried. It assumes that you have 3 "rows" in your data:
DECLARE @table TABLE (defendant VARCHAR(MAX)) ;
INSERT INTO
@table (defendant)
SELECT
'George Greiner' + CHAR(13) + CHAR(10) + '1234 Market Street' + CHAR(13) + CHAR(10) + 'Philadelphia, PA 19108'
UNION ALL
SELECT
'RIGDON MILLER & CO REAL ESTATE' + CHAR(13) + CHAR(10) + '1936 SPRUCE ST' + CHAR(13) + CHAR(10) + '19103'
UNION ALL
SELECT
'BRUNSON, MIRIAM' + CHAR(13) + CHAR(10) + '7238 SANSOM ST' + CHAR(13) + CHAR(10) + '19082'
UNION ALL
SELECT
'SMITH, JAMES J' + CHAR(13) + CHAR(10) + '1600 JACKSON ST' + CHAR(13) + CHAR(10) + '19145'
UNION ALL
SELECT
'MERTLE, JESSICA' + CHAR(13) + CHAR(10) + '205-15 ROCK ST' + CHAR(13) + CHAR(10) + '19128'
UNION ALL
SELECT
'JOHNSON, KALI' + CHAR(13) + CHAR(10) + '9815 HALDENMAN AVE' + CHAR(13) + CHAR(10) + '19115'
UNION ALL
SELECT
'PERSAND, GRACE' + CHAR(13) + CHAR(10) + '333 W ABBOTSFORD RD' + CHAR(13) + CHAR(10) + '19144'
UNION ALL
SELECT
'LAW, ANTHONY' + CHAR(13) + CHAR(10) + '333 W ABBOTSFORD RD' + CHAR(13) + CHAR(10) + '19144';
SELECT
IDENTITY(INT, 1, 1) AS def_id,
'|' + REPLACE(defendant, CHAR(13) + CHAR(10), '|') + '|' AS defendant
INTO #defendents
FROM
@table;
WITH cteTally AS
(
SELECT TOP 100
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n
FROM
sys.all_columns C1 CROSS JOIN
sys.all_columns C2
)
SELECT
def_id,
Row_Number() OVER (PARTITION BY T.defendant ORDER BY TALLY.N) AS id,
SUBSTRING(T.defendant, N+1, CHARINDEX('|',T.defendant,N+1)-N-1) AS attribute
INTO #split
FROM
#defendents AS T CROSS JOIN
cteTally AS TALLY
WHERE
Tally.n < LEN(T.defendant) AND
SUBSTRING(T.defendant, tally.n, 1) = '|'
ORDER BY
T.defendant
SELECT
def_id,
MIN(CASE
WHEN id = 1 THEN attribute
ELSE NULL
END) AS NAME,
MIN(CASE
WHEN id = 2 THEN attribute
ELSE NULL
END) AS address,
MIN(CASE
WHEN id = 3 THEN attribute
ELSE NULL
END) AS city_state_postal
FROM
(
SELECT
def_id,
id,
MIN(attribute) AS attribute
FROM
#split
GROUP BY
def_id,
id
) AS attribs
GROUP BY
def_id
DROP TABLE #split
DROP TABLE #defendents
Please also note how I provided the data in a consumable, testable format. Now anyone can test against it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 10, 2009 at 10:37 am
Jack Corbett (11/10/2009)
I think this code does something similar to what you want. It is a split then pivot which you said you tried. It assumes that you have 3 "rows" in your data:
DECLARE @table TABLE (defendant VARCHAR(MAX)) ;INSERT INTO
@table (defendant)
SELECT
'George Greiner' + CHAR(13) + CHAR(10) + '1234 Market Street' + CHAR(13) + CHAR(10) + 'Philadelphia, PA 19108'
UNION ALL
SELECT
'RIGDON MILLER & CO REAL ESTATE' + CHAR(13) + CHAR(10) + '1936 SPRUCE ST' + CHAR(13) + CHAR(10) + '19103'
UNION ALL
SELECT
'BRUNSON, MIRIAM' + CHAR(13) + CHAR(10) + '7238 SANSOM ST' + CHAR(13) + CHAR(10) + '19082'
UNION ALL
SELECT
'SMITH, JAMES J' + CHAR(13) + CHAR(10) + '1600 JACKSON ST' + CHAR(13) + CHAR(10) + '19145'
UNION ALL
SELECT
'MERTLE, JESSICA' + CHAR(13) + CHAR(10) + '205-15 ROCK ST' + CHAR(13) + CHAR(10) + '19128'
UNION ALL
SELECT
'JOHNSON, KALI' + CHAR(13) + CHAR(10) + '9815 HALDENMAN AVE' + CHAR(13) + CHAR(10) + '19115'
UNION ALL
SELECT
'PERSAND, GRACE' + CHAR(13) + CHAR(10) + '333 W ABBOTSFORD RD' + CHAR(13) + CHAR(10) + '19144'
UNION ALL
SELECT
'LAW, ANTHONY' + CHAR(13) + CHAR(10) + '333 W ABBOTSFORD RD' + CHAR(13) + CHAR(10) + '19144';
SELECT
IDENTITY(INT, 1, 1) AS def_id,
'|' + REPLACE(defendant, CHAR(13) + CHAR(10), '|') + '|' AS defendant
INTO #defendents
FROM
@table;
WITH cteTally AS
(
SELECT TOP 100
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n
FROM
sys.all_columns C1 CROSS JOIN
sys.all_columns C2
)
SELECT
def_id,
Row_Number() OVER (PARTITION BY T.defendant ORDER BY TALLY.N) AS id,
SUBSTRING(T.defendant, N+1, CHARINDEX('|',T.defendant,N+1)-N-1) AS attribute
INTO #split
FROM
#defendents AS T CROSS JOIN
cteTally AS TALLY
WHERE
Tally.n < LEN(T.defendant) AND
SUBSTRING(T.defendant, tally.n, 1) = '|'
ORDER BY
T.defendant
SELECT
def_id,
MIN(CASE
WHEN id = 1 THEN attribute
ELSE NULL
END) AS NAME,
MIN(CASE
WHEN id = 2 THEN attribute
ELSE NULL
END) AS address,
MIN(CASE
WHEN id = 3 THEN attribute
ELSE NULL
END) AS city_state_postal
FROM
(
SELECT
def_id,
id,
MIN(attribute) AS attribute
FROM
#split
GROUP BY
def_id,
id
) AS attribs
GROUP BY
def_id
DROP TABLE #split
DROP TABLE #defendents
Please also note how I provided the data in a consumable, testable format. Now anyone can test against it.
If I went this route would I run this query and then UPDATE my current table with the correct data?
Also is it possible only pull the zip code as opposed pull everything after the 2nd hard return? Along those same lines if there is not a second hard return and the data is as such George Greiner CHR(13) CHR(10) 19038 it puts the zip in the wrong field. I just am trying to test all outcomes.
Thanks you very much for this as it functions much much better than the other one I tried. I will go over the code provided and learn from it and how to adjust it as that is the way I learn the quickest anyway.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply