June 17, 2014 at 9:28 pm
cbrammer1219 (6/15/2014)
Here's the file being processed.
It's really tough to hit a rolling donut. Where'd the file go?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2014 at 9:23 am
I am not sure where it went. Here it is again
June 18, 2014 at 3:20 pm
I am not sure the original request is still open, but assuming it is, I would definitely go with a CLR scalar function for such a request. Operations on text data are a breeze in a language like C# and, even for scalar functions, the performance is quite good. It was not too difficult for me to code and deploy a CLR that can do the before-space-character-counting and, no joke, my C# skills are rudimentary at best. Below is the C# code for the function, which just needs to be compiled and then added as an assembly to a database.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public class Count_Char_Between_Space
{
[SqlFunction()]
public static SqlString CountCharBetweenSpace(string ColumnText)
{
int count = 0;
System.Text.StringBuilder sb = new System.Text.StringBuilder();
foreach (char c in ColumnText)
{
if (!char.IsWhiteSpace(c))
{
count++;
}
if (char.IsWhiteSpace(c))
{
sb.AppendLine(count.ToString());
count = 0;
}
}
return sb.ToString();
}
}
Once the assembly is loaded, then a scalar function needs to be created to use it:
CREATE FUNCTION [dbo].CountCharBetweenSpace(@string nvarchar(max))
RETURNS nvarchar(max)
EXTERNAL NAME Count_Char_Between_Space.Count_Char_Between_Space.CountCharBetweenSpace
Using the original data, the function handles the requirements as originally specified.
Create table #SomeValue ( SomeValue varchar(50) )
insert into #SomeValue values
('5 555 55 1 NULL'),
('5 555 55 2 NULL'),
('6 5 5555 hhh 1 NULL'),
('6 5 5555 hhh 2 NULL'),
('6 5 5555 hhh 3 NULL'),
('8 5555 5 lll 1 NULL'),
('8 5555 5 lll 2 NULL'),
('8 5555 5 lll 3 NULL'),
('9 55555 1 k 1 NULL'),
('9 55555 1 k 2 NULL'),
('9 55555 1 k 3 NULL'),
('10 1888 555 jjj55 1 NULL'),
('10 1888 555 jjj55 2 NULL'),
('10 1888 555 jjj55 3 NULL')
SELECT [dbo].CountCharBetweenSpace(isnull(SomeValue,'')) ,SomeValue FROM #SomeValue
June 18, 2014 at 3:44 pm
So I don't have to do all of that just to see, what do you get as an output from all of that?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 19, 2014 at 7:54 am
This is the output:
(No column name)SomeValue
13215 555 55 1 NULL
13215 555 55 2 NULL
11431 6 5 5555 hhh 1 NULL
11431 6 5 5555 hhh 2 NULL
114316 5 5555 hhh 3 NULL
141318 5555 5 lll 1 NULL
141318 5555 5 lll 2 NULL
141318 5555 5 lll 3 NULL
151119 55555 1 k 1 NULL
151119 55555 1 k 2 NULL
151119 55555 1 k 3 NULL
2435110 1888 555 jjj55 1 NULL
2435110 1888 555 jjj55 2 NULL
2435110 1888 555 jjj55 3 NULL
June 19, 2014 at 8:28 am
I have gotten this far, I have the calls separated by the spaces. Now it's joining on the contacts phone number.
June 19, 2014 at 8:38 am
My problem is that I really don't understand what you're trying to do. This start out with "counting characters in a string before a space" and I showed you how to import the file with the correct fields (although I didn't presume to name them because I don't have the record layout). The 5th field is a composite field that is dependent on what type of record the CDR is and could easily be split correctly without have to "count characters before a space" but we need the record layout information for the field.
What is it that you're having problems with?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 19, 2014 at 10:01 am
I used the code you supplied and it works correctly, but the contact information is from a different table(alcont) this table is from a completely different system, I AM INCLUDING A SAMPLE OF THE DATA IN EXCEL.
Here is the sample code I have tried to use. It is returning only outbound calls, not incoming and the contact names aren't being displayed.
WITH
AcctJoin as
(select DISTINCT c.AR_COMPANY_NAME as CompanyName,REPLACE(REPLACE(REPLACE(REPLACE(a.CONTACT_PHONE,'(',''),')',''),' ',''),'-','') as phone
from ARLSQ01.dsg.[dbo].DICE_ALCONCT a
join ARLSQ01.dsg.[dbo].DICE_ARSUBHD b
on a.ACCOUNT_NUMBER=b.ACCOUNT_NUMBER
join ARLSQ01.dsg.[dbo].DICE_ARCUSTMN c
on b.AR_NUMBER=c.AR_NUMBER
WHERE RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(a.CONTACT_PHONE,'(',''),')',''),' ',''),'-','')) <> ''
UNION ALL
SELECT e.ar_company_name, REPLACE(REPLACE(REPLACE(REPLACE(d.PHONE_NUMBER,'(',''),')',''),' ',''),'-','')
from ARLSQ01.dsg.[dbo].DICE_ARCONTCT d
join ARLSQ01.dsg.[dbo].DICE_ARCUSTMN e
on d.AR_NUMBER=e.AR_NUMBER
WHERE RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(d.PHONE_NUMBER,'(',''),')',''),' ',''),'-','')) <> '')
June 19, 2014 at 1:08 pm
Jeff Moden (6/19/2014)
My problem is that I really don't understand what you're trying to do. This start out with "counting characters in a string before a space" and I showed you how to import the file with the correct fields (although I didn't presume to name them because I don't have the record layout). The 5th field is a composite field that is dependent on what type of record the CDR is and could easily be split correctly without have to "count characters before a space" but we need the record layout information for the field.What is it that you're having problems with?
I've compared this to the MyTel specs I've found and it looks like it is coming from an extraction utility, not a raw CDR. Too busy at the moment to attend to it but will ask around. One thing for certain, counting characters and spaces is not the right approach here.
😎
June 19, 2014 at 4:21 pm
Eirikur Eiriksson (6/19/2014)
Jeff Moden (6/19/2014)
My problem is that I really don't understand what you're trying to do. This start out with "counting characters in a string before a space" and I showed you how to import the file with the correct fields (although I didn't presume to name them because I don't have the record layout). The 5th field is a composite field that is dependent on what type of record the CDR is and could easily be split correctly without have to "count characters before a space" but we need the record layout information for the field.What is it that you're having problems with?
I've compared this to the MyTel specs I've found and it looks like it is coming from an extraction utility, not a raw CDR. Too busy at the moment to attend to it but will ask around. One thing for certain, counting characters and spaces is not the right approach here.
😎
Got a link for the specs?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 21, 2014 at 9:51 am
Jeff Moden (6/19/2014)
Eirikur Eiriksson (6/19/2014)
Jeff Moden (6/19/2014)
My problem is that I really don't understand what you're trying to do. This start out with "counting characters in a string before a space" and I showed you how to import the file with the correct fields (although I didn't presume to name them because I don't have the record layout). The 5th field is a composite field that is dependent on what type of record the CDR is and could easily be split correctly without have to "count characters before a space" but we need the record layout information for the field.What is it that you're having problems with?
I've compared this to the MyTel specs I've found and it looks like it is coming from an extraction utility, not a raw CDR. Too busy at the moment to attend to it but will ask around. One thing for certain, counting characters and spaces is not the right approach here.
😎
Got a link for the specs?
Only have a printed copy, will try to find an online one.
😎
June 21, 2014 at 12:12 pm
Appreciate that but don't do it on my account. I'm thinking that the OP should have such a thing. If not, then we know the real problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 61 through 71 (of 71 total)
You must be logged in to reply to this topic. Login to reply