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
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 1:57 PM
Points: 105, Visits: 240
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
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 1:57 PM
Points: 105, Visits: 240
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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 2,533, Visits: 7,097
You can use the DelimitedSplit8K for this.
Post #1579870
Posted Thursday, June 12, 2014 8:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 1:57 PM
Points: 105, Visits: 240
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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:27 AM
Points: 4,046, Visits: 9,201
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.
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?

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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:27 AM
Points: 4,046, Visits: 9,201
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.
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?

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 @ 11:55 AM
Points: 1,943, Visits: 20,178
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
Post #1580103
Posted Thursday, June 12, 2014 9:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 1:57 PM
Points: 105, Visits: 240
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 @ 11:55 AM
Points: 1,943, Visits: 20,178
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
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: Wednesday, December 17, 2014 9:16 AM
Points: 1,391, Visits: 644
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