June 12, 2014 at 1:45 pm
Did you try the function I posted above? sorry for the formatting here. I can't upload a screenshot...
c1 Characters
-------------------------------------------------------------------------------------------------------------------------------- --------------------
0154 6548647 11235 475
0154 65478647 11235 485
01464 64788647 11835 1654 5854
01464 64788647 1184 132 5843
01464 64788647 1124 165 5843
0154 65478647 11235 485
June 12, 2014 at 1:59 pm
Jeff your code actually did the trick, now I have to get each value into a different columns, you have them concatenated together, but this is a great start..
June 12, 2014 at 2:04 pm
cbrammer1219 (6/12/2014)
This actually does it, now I have to get each value into a different columns, you have them concatenated together, but this is a great start..
This is not only very poor performance wise (scalar functions are very slow), this is not going to work when you have more than 9 characters in a row.
Take the code I posted and throw the results into a cross tab (Like Luis did).
_______________________________________________________________
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/
June 12, 2014 at 2:20 pm
Like this.
if OBJECT_ID('tempdb..#something') is not null
drop table #Something
create table #Something
(
SomeValue varchar(100)
)
insert #Something
select '0154 6548647 11235' union all
select '0154 65478647 11235' union all
select '01464 64788647 11835 1654' union all
select '01464 64788647 1184 132' union all
select '01464 64788647 1124 165' union all
select '0154 65478647 11235 '
select * from #Something
select SomeValue
,MAX(Case when ItemNumber = 1 then LEN(Item) end) as ItemLength1
,MAX(Case when ItemNumber = 2 then LEN(Item) end) as ItemLength2
,MAX(Case when ItemNumber = 3 then LEN(Item) end) as ItemLength3
,MAX(Case when ItemNumber = 4 then LEN(Item) end) as ItemLength4
,MAX(Case when ItemNumber = 5 then LEN(Item) end) as ItemLength5
,MAX(Case when ItemNumber = 6 then LEN(Item) end) as ItemLength6
,MAX(Case when ItemNumber = 7 then LEN(Item) end) as ItemLength7
,MAX(Case when ItemNumber = 8 then LEN(Item) end) as ItemLength8
,MAX(Case when ItemNumber = 9 then LEN(Item) end) as ItemLength9
,MAX(Case when ItemNumber = 10 then LEN(Item) end) as ItemLength10
from #Something
cross apply dbo.DelimitedSplit8K(SomeValue, ' ')
group by SomeValue
_______________________________________________________________
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/
June 12, 2014 at 2:37 pm
I can't put a where clause on this, before the cross apply?? It gives an error incorrect syntax before cross???
if OBJECT_ID('AA_Helper.dbo.MitelCallTrace') is not null
drop table AA_Helper.dbo.MitelCallTrace
create table AA_Helper.dbo.MitelCallTrace
(
calldata varchar(400)
)
INSERT INTO AA_Helper.dbo.CallTrace (cDate,
cStartTime,
cDuration,
calledparty,
cTimetoAnswer,
origCall,
cDestination,
cDigitsDialed,
callingparty,
cSystemID,
cANI,
cDNIS,
cCallID,
cCallIDSeq)
SELECT
RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(calldata,0,7),'-',''),'%',''),'+',''),'/','-'))) as cDate,
RTRIM(LTRIM(SUBSTRING(calldata,7,9))) as cStartTime,
RTRIM(LTRIM(SUBSTRING(calldata,19,9))) as cDuration,
RTRIM(LTRIM(SUBSTRING(calldata,29,6))) as calledparty,
RTRIM(LTRIM(SUBSTRING(calldata,41,6))) as origCall,
RTRIM(LTRIM(SUBSTRING(calldata,47,19))) as cDestination,
REPLACE(RTRIM(LTRIM(SUBSTRING(calldata,34,7))),'*','') as cTimetoAnswer,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(SUBSTRING(calldata,41,28))),'A',''),'B',''),'I',''),'E',''),'T',''),'*','') as cDigitsDialed,
RTRIM(LTRIM(SUBSTRING(calldata,69,6))) as callingparty,
RTRIM(LTRIM(SUBSTRING(calldata,108,3))) as cSystemID,
RTRIM(LTRIM(SUBSTRING(calldata,112,19))) as cANI,
RTRIM(LTRIM(SUBSTRING(calldata,124,21))) as cDNIS,
RTRIM(LTRIM(SUBSTRING(calldata,148,15))) as cCallID,
RTRIM(LTRIM(SUBSTRING(calldata,162,2))) as cCallIDSeq
FROM [dbo].[MitelCallTrace] mt
where len(calldata) <> 0 and SUBSTRING(calldata,0,7) <> ''
cross apply dbo.DelimitedSplit8K(calldata, ' ')
group by calldata
June 12, 2014 at 2:59 pm
cbrammer1219 (6/12/2014)
I can't put a where clause on this, before the cross apply?? It gives an error incorrect syntax before cross???
No you can't put it before.
FROM [dbo].[MitelCallTrace] mt
cross apply dbo.DelimitedSplit8K(calldata, ' ')
where len(calldata) <> 0 and SUBSTRING(calldata,0,7) <> ''
group by calldata
You should take a look at the articles in my signature about APPLY so you understand what that is doing.
_______________________________________________________________
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/
June 12, 2014 at 3:04 pm
Ok, that ran, but it is terribly slow, The function from Jeff returned the data fast, although within characters it put the counts in one row, and would like to separate them into their own columns. It has actually locked up Studio Management.
June 12, 2014 at 3:22 pm
cbrammer1219 (6/12/2014)
Ok, that ran, but it is terribly slow, The function from Jeff returned the data fast, although within characters it put the counts in one row, and would like to separate them into their own columns. It has actually locked up Studio Management.
Huh??? You are saying an iTVF is running slow and a scalar function with a loop inside is running fast? There is something else going on here.
Do you have that same where clause in both?
where len(calldata) <> 0 and SUBSTRING(calldata,0,7) <> ''
Depending on the amount of data that could be horribly slow because it is nonSARGable.
_______________________________________________________________
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/
June 12, 2014 at 3:24 pm
cbrammer1219 (6/12/2014)
Ok, that ran, but it is terribly slow, The function from Jeff returned the data fast, although within characters it put the counts in one row, and would like to separate them into their own columns. It has actually locked up Studio Management.
You didn't do anything funky like put RTRIM(calldata) in the function call did you?
_______________________________________________________________
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/
June 12, 2014 at 3:51 pm
No...just as I posted it..
June 12, 2014 at 3:54 pm
Did you even try to change the SSIS package to avoid all these complications?
June 12, 2014 at 3:57 pm
No I figured I would get the tsql working first, and then attempt that change.
June 12, 2014 at 4:01 pm
cbrammer1219 (6/12/2014)
No I figured I would get the tsql working first, and then attempt that change.
If you fix the data import, you don't need to work that hard on the t-sql.
June 14, 2014 at 7:17 am
Luis Cazares (6/12/2014)
cbrammer1219 (6/12/2014)
No I figured I would get the tsql working first, and then attempt that change.If you fix the data import, you don't need to work that hard on the t-sql.
+1 GAZILLION-TRILLION-BILLION!!!!
At the time I first looked at it, only 2 people had looked at the text file that the OP posted. As with most telephone systems, it's nothing more than a mostly [font="Arial Black"]simple fixed-field-format text file [/font]that can be easily resolved/imported/parsed either by using a BULK INSERT of the entire row and using SUBSTRING to parse the fields, or by using BULK INSERT with a BCP format file to do the parsing and most of the data validation for you. For the most part, there's nothing complicated about importing this file in a columnar fashion to a staging table for validation.
The proverbial fly-in-the-ointment is in the 5th field of the file. It contains many different types of data depending on (I would imagine) the nature of the CDR (Call Detail Record, which is what the file contains). Here's a sample of what those damned things look like and only someone with the "book" on what the record layout and field definitions of the CDRs would be able to interpret. Even though there are spaces in this field, they should NOT be interpreted as column delimiters in the overall columnar import of the CDRs. They are, however, column delimiters within the field and will need a bit of post processing. Such post processing would be determined by what "the book" says about the content of this 5th field.
1403 17815051438
2422 16176660248
T7818592700 2074
T2074 15088720422
T2074 915088720422
P105 101 101
T8*029 8*042705 2704 91781
1 17818592702
I'll be back...
--Jeff Moden
June 14, 2014 at 9:21 am
Jeff Moden (6/14/2014)
I'll be back...
:smooooth:
It certainly looks, smells and tastes like a CDR, question is what is the originating system, record type and record format? Many CDR formats are slightly tricky, multi record type, combination of fixed and variable fields or even mix of text and binary such as some ASN.1.
Stay tuned....
😎
Viewing 15 posts - 31 through 45 (of 71 total)
You must be logged in to reply to this topic. Login to reply