Click here to monitor SSC
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
cbrammer1219
cbrammer1219
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 368
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.
cbrammer1219
cbrammer1219
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 368
Anyone know how to do this, or maybe a link that gives a example of something close?
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6736 Visits: 17713
You can use the DelimitedSplit8K for this.
Cool
cbrammer1219
cbrammer1219
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 368
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
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8544 Visits: 18132
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.
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
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8544 Visits: 18132
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.
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
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 33040
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
cbrammer1219
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 368
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
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 33040
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
Sarah Wagner
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1560 Visits: 715
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.
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