Counting the characters in a string before a space

Question

Counting the characters in a string before a space

Viewing 15 posts - 16 through 30 (of 71 total)

You must be logged in to reply to this topic. Login to reply

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 1

J Livingston SQL (6/12/2014)
cbrammer1219 (6/12/2014)
These are phone numbers, and I am using them to determine if it is a inbound call or outbound,
For example....
3333 917899999 3210
3333 is the extension the call came into 917899999 is the it was transferred to and 3210 is where the final transfer ended up.
ok...maybe we can help you a little.
as Luis mentioned above, please provide some create table / insert data scripts with sample data...and your expected results for the sample data.

Let me apologize for the poorly formatted result set now, new to the forum and not sure how to format, but the underlined values(cDigitsDialed) are the ones I am trying to count the characters before the spaces, sometimes only 1 space and sometime multiple. So the first set of characters in cDigitsDialed should be the (origCall) and the second set of numbers should be in the (cDestination) and anything of the cDigitsDialed starting with (91) is a outbound call or a (1) this I can get but the counting of the characters before the space has me, confused and frustrated.

CREATE TABLE [dbo].[MitelCallTrace](

[calldata] [varchar](400) NULL

) ON [PRIMARY]

GO

INSERT INTO AA_Helper.dbo.CallTrace (cDate,

cStartTime,

cDuration,

callingparty,

cTimetoAnswer,

origCall,

cDestination,

cDigitsDialed,

calledparty,

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 callingparty,

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 calledparty,

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]

where

len(calldata) <> 0 and SUBSTRING(calldata,0,7) <> ''

CREATE TABLE [dbo].[CallTrace](

[cDate] [nvarchar](10) NULL,

[cStartTime] [nvarchar](8) NULL,

[cDuration] [nvarchar](9) NULL,

[callingparty] [varchar](8) NULL,

[origCall] [nvarchar](35) NULL,

[cDestination] [nvarchar](35) NULL,

[cTimetoAnswer] [nvarchar](5) NULL,

[cDigitsDialed] [varchar](27) NULL,

[calledparty] [varchar](8) NULL,

[cSystemID] [varchar](3) NULL,

[cANI] [varchar](19) NULL,

[cDNIS] [varchar](21) NULL,

[cCallID] [varchar](13) NULL,

[cCallIDSeq] [varchar](1) NULL

) ON [PRIMARY]

GO

cDate cStartTime cDuration callingparty origCall cDestination cTimetoAnswer cDigitsDialed calledpartycSystemID

05-03 00:33:47 000:00:532422 242217812905723 2422 17812905723T223555

05-03 00:36:55 000:00:22T615 T7818592700 20730001 7818592700 2073 11127555

05-03 00:37:03 000:00:14T616 T207316178765800 2073 16178765800 T222555

05-03 00:37:03 000:00:16T616 T20739161787658000008 2073 916178765800T222555

05-03 00:36:33 000:00:551403 140317815051438 1403 17815051438T223555

05-03 00:37:18 000:00:132422 242216176660248 2422 16176660248T220555

05-03 00:37:10 000:00:54T617 T7818592700 20740002 7818592700 207411125555

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 2

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.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

I noticed that you didn't only have spaces and you have tabs as well.

Would this help you?

SELECT MAX( CASE WHEN ItemNumber = 1 THEN LTRIM(Item) END) Col1

,MAX( CASE WHEN ItemNumber = 2 THEN LTRIM(Item) END) Col2

,MAX( CASE WHEN ItemNumber = 3 THEN LTRIM(Item) END) Col3

,MAX( CASE WHEN ItemNumber = 4 THEN LTRIM(Item) END) Col4

,MAX( CASE WHEN ItemNumber = 5 THEN LTRIM(Item) END) Col5

,MAX( CASE WHEN ItemNumber = 6 THEN LTRIM(Item) END) Col6

,MAX( CASE WHEN ItemNumber = 7 THEN LTRIM(Item) END) Col7

,MAX( CASE WHEN ItemNumber = 8 THEN LTRIM(Item) END) Col8

,MAX( CASE WHEN ItemNumber = 9 THEN LTRIM(Item) END) Col9

,MAX( CASE WHEN ItemNumber = 10 THEN LTRIM(Item) END) Col10

FROM MitelCallTrace

CROSS APPLY dbo.DelimitedSplit8K(calldata, CHAR(9))s

GROUP BY calldata

EDIT: I forgot to add the GROUP BY

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 4

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.

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 5

No that didn't work, all it did was return NULL values for every column 1 through 10

Jeff Shurak SSCrazy Points: 2361 More actions · Answer 6

What kind of output are you looking for?

declare @string varchar(500) = '1564 163546 10352 124 142587'

declare @nSpaces int = LEN(@string) - LEN(REPLACE(@string,' ',''))

declare @i int

while @nSpaces > 0

BEGIN

SELECT @i = CHARINDEX(' ',@string,0) - 1

PRINT CAST(@i as varchar(5))

SET @string = SUBSTRING(@string,@i + 2,LEN(@string))

SET @nSpaces -= 1

END

PRINT CAST(LEN(@STRING) AS VARCHAR)

Luis Cazares SSC Guru Points: 183698 More actions · Answer 7

cbrammer1219 (6/12/2014)
No that didn't work, all it did was return NULL values for every column 1 through 10

Well, that's not what I've got using the data that you posted.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Shurak SSCrazy Points: 2361 More actions · Answer 8

Updating as a function....probably not very efficient for large data sets...

--test table creation

CREATE TABLE TEST (c1 varchar(128))

--dummy data

insert into test (c1) values ('0154 6548647 11235')

insert into test (c1) values ('0154 65478647 11235')

insert into test (c1) values ('01464 64788647 11835 1654')

insert into test (c1) values ('01464 64788647 1184 132')

insert into test (c1) values ('01464 64788647 1124 165')

insert into test (c1) values ('0154 65478647 11235')

--function creation

CREATE FUNCTION dbo.CharCounter (@string varchar(128))

returns varchar(20)

as

begin

declare @nSpaces int = LEN(@string) - LEN(REPLACE(@string,' ',''))

declare @i int, @out varchar(20)

while @nSpaces > 0

BEGIN

SELECT @i = CHARINDEX(' ',@string,0) - 1

SET @out = CONCAT(@out,CAST(@i as varchar))

--PRINT CAST(@i as varchar(5))

SET @string = SUBSTRING(@string,@i + 2,LEN(@string))

SET @nSpaces -= 1

END

SET @out = CONCAT(@out,CAST(LEN(@string) as varchar))

RETURN @out

END

--function call

select c1,dbo.CharCounter(c1) as [Characters]

FROM TEST;

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 9

I don't need to parse the MitelCallTrace table that is a staging table I am inserting the data from a flatfile into, I am processing the parsed data in CallTrace and the cDigitsDialed field is the only field I need to do the count of chars before the spaces.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 10

...sorry bin gone for a while on this thread............and a lot of follow up posts

have you / can you provide sample data for this table:

CREATE TABLE [dbo].[MitelCallTrace](

[calldata] [varchar](400) NULL

) ON [PRIMARY]

I don't think you need all the current parsing you are going....maybe wrong

anyways...

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

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 11

I have attached a flatfile with the data I am processing in SSIS and inserting into MitelCallTrace, then parsing it and inserting it into CallTrace.

I have posted the create tables.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 12

cbrammer1219 (6/12/2014)
I have attached a flatfile with the data I am processing in SSIS and inserting into MitelCallTrace, then parsing it and inserting it into CallTrace.
I have posted the create tables.

If you're using SSIS, why don't you divide the columns from the start instead of having a single large column?

I'm attaching an example of a package to import your file into a table. The connection string for the destination must be changed as well as the file extension (into .dtsx).

Here's the DDL of the destination table:

CREATE TABLE [dbo].[TestImport](

[Column 1] [varchar](6) NULL,

[Column 2] [varchar](10) NULL,

[Column 3] [varchar](11) NULL,

[Column 4] [varchar](8) NULL,

[Column 5] [varchar](5) NULL,

[Column 6] [varchar](26) NULL,

[Column 7] [varchar](2) NULL,

[Column 8] [varchar](38) NULL,

[Column 9] [varchar](6) NULL,

[Column 10] [varchar](21) NULL,

[Column 11] [varchar](19) NULL,

[Column 12] [varchar](9) NULL,

[Column 13] [varchar](49) NULL

)

NOTE: Best practices weren't followed as this was just a test and I don't have complete information.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 13

SELECT *, PATINDEX('% %',LTRIM(RTRIM(cDigitsDialed))) FROM CallTrace

I am able to the first count of chars before the space using this, but just the first set, if there is more than one space it doesn't work...Any Ideas?

Jeff Shurak SSCrazy Points: 2361 More actions · Answer 14

PATINDEX finds the first occurrence of the pattern. With CHARINDEX you can specify a starting location.

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 15

It actually gave me the count of chars, before the first space...CharIndex has only given me the count of spaces within the string.