SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Counting the characters in a string before a space


Counting the characters in a string before a space

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216610 Visits: 41986
cbrammer1219 (6/14/2014)
Tell me about it I have told them before any projects are started the data needs cleaned up, these tables don't even have Pk or fk, they b Ave me joining on tables with no relation at all and I try to tell them they are reporting incorrectly. They won't listen. You should see the customer and contact tables, there is no data validation when input to the database. It's a nightmare. ...


The thing I learned SQL on was CDRs so I feel your pain on this. Have they provided you with a Record Layout that you could share? I think it's really important because you missed the data in the file that starts at character 165. I also think that the record layout would contain more information about what the infamous Col05 contains so that we could pull off the perfect split for you. It would also help us define what the PK needs to be so that you can check for duplicate calls, which AT&T and most of the other "Biggees" are nortorious for providing.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40938 Visits: 19477
cbrammer1219 (6/12/2014)
Yes, I am not looking to count the spaces, I want the count of characters before each space, I can find the spaces, but I am needing for example 777 8888 99999 ---> count of first set of chars before the space(3)....count of second bf space(4) and then (5)...final count of chars.


Is this coming from specs or is it a observed pattern? Have the feeling you might be chasing a red herring here, any CDR format should contain these information explicitly, not implied as this.

Understanding the logic of these record is mandatory for processing them, such as call types A B C D, Transfers, Trunks etc.
Cool
cbrammer1219
cbrammer1219
SSC Eights!
SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)

Group: General Forum Members
Points: 994 Visits: 427
I don't need all of the data, I am inserting the data from my original post to create a SSRS report, the data is coming from the flatfile which I am loading into SQL 2012 table. The other fields that aren't being inserted aren't needed for my report, this is something the 3rd party system is doing. Called Mettel...
J Livingston SQL
J Livingston SQL
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12279 Visits: 37558
cbrammer1219 (6/14/2014)
I will give this a try in a little while, I appreciate all that have stuck through this post. I will mark answered if this is a solution or even close.


and the answer is...???

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40938 Visits: 19477
cbrammer1219 (6/15/2014)
I don't need all of the data, I am inserting the data from my original post to create a SSRS report, the data is coming from the flatfile which I am loading into SQL 2012 table. The other fields that aren't being inserted aren't needed for my report, this is something the 3rd party system is doing. Called Mettel...


Just step a way from counting characters and spaces for a moment, could you tell us what you want to report on? If it is something like incoming call/outgoing call/transferred call, originating/terminating numbers, then you are indeed chasing ghosts and red herrings in flocks as the fields being ignored would hold all the information you are after.

And there is more, a CDR will hold multiple records for a single call, A/B record for the call initiation, another for the pick-up/answer etc. Without knowing this logic and how to interpret the different record types this data is not going to make much sense.
Cool
cbrammer1219
cbrammer1219
SSC Eights!
SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)

Group: General Forum Members
Points: 994 Visits: 427
Yes that is what I am reporting on, for each extension. The count of incoming and out going call for each extension displaying customer contact and call duration.
cbrammer1219
cbrammer1219
SSC Eights!
SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)

Group: General Forum Members
Points: 994 Visits: 427
Here's the file being processed.
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40938 Visits: 19477
cbrammer1219 (6/15/2014)
Here's the file being processed.


Now we are getting somewhere, looks like an utility extract from a CDR, possibly MiTel (hence the table name in the earlier post), will look into it.
Cool
cbrammer1219
cbrammer1219
SSC Eights!
SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)

Group: General Forum Members
Points: 994 Visits: 427
Just curious could I create a lookup table with all the extension in it and then insert that into the CallTrace table where the extension and do the same for the Customer table, because right now there are no PK, FK, it is the worst DB structure I have ever worked with, and yet they want miracles; After this I am going to insist on taking the time to work with my peer to begin creating a datamart, any suggestion for a beginning SQL to accomplish this?
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40938 Visits: 19477
cbrammer1219 (6/17/2014)
Just curious could I create a lookup table with all the extension in it and then insert that into the CallTrace table where the extension and do the same for the Customer table, because right now there are no PK, FK, it is the worst DB structure I have ever worked with, and yet they want miracles; After this I am going to insist on taking the time to work with my peer to begin creating a datamart, any suggestion for a beginning SQL to accomplish this?

Welcome to the real world, and mind you it is not going to get better, when ever you think you've seen the worst, it gets worse. That's the business and one better get used to it.

I'm trying to find specs on the format, MiTel isn't to common any more, will let you know.

You shouldn't need an extension table unless you can relate/tie the extension to a business entity/value. But if so, in a data-mart/dw feeding into a cube, that would probably become a dimension.

The sql part is tertiary, first business requirements then data models. Only after those steps one starts talking sql.
Cool
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search