Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Counting the characters in a string before a space Expand / Collapse
Author
Message
Posted Wednesday, June 11, 2014 5:41 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:59 PM
Points: 57, Visits: 141
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.
Post #1579858
Posted Wednesday, June 11, 2014 6:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:59 PM
Points: 57, Visits: 141
Anyone know how to do this, or maybe a link that gives a example of something close?
Post #1579861
Posted Wednesday, June 11, 2014 8:45 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:12 PM
Points: 1,233, Visits: 3,577
You can use the DelimitedSplit8K for this.
Post #1579870
Posted Thursday, June 12, 2014 8:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:59 PM
Points: 57, Visits: 141
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.

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
Post #1580089
Posted Thursday, June 12, 2014 9:01 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 3,317, Visits: 7,163
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 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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1580101
Posted Thursday, June 12, 2014 9:03 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 3,317, Visits: 7,163
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.

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

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



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1580102
Posted Thursday, June 12, 2014 9:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:43 PM
Points: 1,877, Visits: 18,460
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 !
__________________________________________________________________
Post #1580103
Posted Thursday, June 12, 2014 9:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:59 PM
Points: 57, Visits: 141
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.
Post #1580105
Posted Thursday, June 12, 2014 9:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:43 PM
Points: 1,877, Visits: 18,460
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 !
__________________________________________________________________
Post #1580110
Posted Thursday, June 12, 2014 9:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 1,233, Visits: 535
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.
Post #1580119
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse