Counting the characters in a string before a space

Question

Post reply

Counting the characters in a string before a space

cbrammer1219

SSCarpal Tunnel

Points: 4476
More actions
June 11, 2014 at 5:41 pm

#286422

I have searched everywhere and about every forum. I am trying to count the characters in a sting before a space. Here is the example of what I am trying to accomplish.
"2073 9187463 2700' 4 7 4, the string character count is 4 before the space, 7 is the count before the next space and the last is the last in the string, if there was more characters within this string for example....'2073 9187463 2700 7023 6044567' it would return the number of characters in the string before the space and at the very end of it.

Viewing 15 posts - 1 through 15 (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

Anyone know how to do this, or maybe a link that gives a example of something close?

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 2

You can use the DelimitedSplit8K [/url] for this.

😎

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 3

This doesn't work for what I am wanting, it is counting the spaces, It doesn't count the characters before the spaces, as you see in the data below for 5...It should be 3 for the first set of 5's and 2 for the 2nd set of 5's. I am not sure what this is doing with the numbers, instead of a comma I changed to (' ') a space.

5555 55 1NULL

5555 55 2NULL

65 5555 hhh1NULL

65 5555 hhh2NULL

65 5555 hhh3NULL

85555 5 lll 1NULL

85555 5 lll 2NULL

85555 5 lll 3NULL

955555 1 k1NULL

955555 1 k2NULL

955555 1 k3NULL

101888 555 jjj551NULL

101888 555 jjj552NULL

101888 555 jjj553NULL

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

I just wanted to play a little bit with the DelimitedSplit8k and this is what came out.

CREATE FUNCTION [dbo].[ItemsLength]

(

@pString [varchar](8000),

@pDelimiter [char](1)

)

RETURNS TABLE WITH SCHEMABINDING

AS

RETURN

--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...

-- enough to cover NVARCHAR(4000)

WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), --10E+1 or 10 rows

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front

-- for both a performance gain and prevention of accidental "overruns"

SELECT TOP (ISNULL(DATALENGTH(@pString),0) + 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

),

cteDelims(N, row) AS (--==== This returns position of each delimiter

SELECT 0 , 1 UNION ALL

SELECT t.N, ROW_NUMBER() OVER(ORDER BY t.N) + 1 FROM cteTally t WHERE SUBSTRING(@pString + ' ',t.N,1) = @pDelimiter

)

SELECT d2.N - d1.N - 1

FROM cteDelims d1

JOIN cteDelims d2 ON d1.row = d2.row - 1

;

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

cbrammer1219 (6/12/2014)
This doesn't work for what I am wanting, it is counting the spaces, It doesn't count the characters before the spaces, as you see in the data below for 5...It should be 3 for the first set of 5's and 2 for the 2nd set of 5's. I am not sure what this is doing with the numbers, instead of a comma I changed to (' ') a space.
5555 55 1NULL
5555 55 2NULL
65 5555 hhh1NULL
65 5555 hhh2NULL
65 5555 hhh3NULL
85555 5 lll 1NULL
85555 5 lll 2NULL
85555 5 lll 3NULL
955555 1 k1NULL
955555 1 k2NULL
955555 1 k3NULL
101888 555 jjj551NULL
101888 555 jjj552NULL
101888 555 jjj553NULL

Could you post the code that you used along with some sample data in a consumable format (DDL and insert statements)?

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

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

out of curiostity...having got the character count before spaces.....what are you intending to do with the results?

________________________________________________________________
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 7

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.

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

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.

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

Sarah Wagner Hall of Fame Points: 3178 More actions · Answer 9

Maybe I missed the boat, but is there a reason you aren't using a simple charindex?

select charindex(' ', field) returns where the first space is located, you can use that to start the next search through the string

select charindex(' ', field) -- FirstSpace and number of chars.

, charindex(' ', field, charindex(' ', field)+1) -- SecondSpace

, charindex(' ', field, charindex(' ', field)+1) - charindex(' ', field) -- Nbr of chars between first and second space

and so on... It can get pretty ugly, but if it is just ext + phone + ext then you should only have 3 spaces in your input.

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

cbrammer1219 (6/12/2014)
This doesn't work for what I am wanting, it is counting the spaces, It doesn't count the characters before the spaces, as you see in the data below for 5...It should be 3 for the first set of 5's and 2 for the 2nd set of 5's. I am not sure what this is doing with the numbers, instead of a comma I changed to (' ') a space.
5555 55 1NULL
5555 55 2NULL
65 5555 hhh1NULL
65 5555 hhh2NULL
65 5555 hhh3NULL
85555 5 lll 1NULL
85555 5 lll 2NULL
85555 5 lll 3NULL
955555 1 k1NULL
955555 1 k2NULL
955555 1 k3NULL
101888 555 jjj551NULL
101888 555 jjj552NULL
101888 555 jjj553NULL

Not quite sure what you used and why it doesn't work but the DelimitedSplit8K function works just fine for this. Also not totally sure what your data looks like because the formatting gets lost. That is one reason it is preferred to post sample data in a consumable format.

with myData as

(

select SomeValue

from (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')) s(SomeValue)

)

select *, LEN(Item) as ItemLength

from myData

cross apply dbo.DelimitedSplit8K(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/

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 11

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 12