count charindex - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

count charindex

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-16 : 05:58:17

DECLARE @x VARCHAR(50)
SELECT @x = 'a534=-\aaaaaaaaa'

how to count all alpha/numeric/symbol in a column?

Hope can help...but advise to wait pros with confirmation...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-16 : 06:00:16

I think without a function it is not possible to get reliable results.

No, you're never too old to Yak'n'Roll if you're too young to die.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-16 : 06:03:19

i don't need all column/row...i just need 1 row 1 column..

Hope can help...but advise to wait pros with confirmation...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-16 : 06:04:49

yes I know.

No, you're never too old to Yak'n'Roll if you're too young to die.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-16 : 06:07:03

webby~! i had a column VARCHAR(MAX), may i know what happen when the character reach 8001?

Hope can help...but advise to wait pros with confirmation...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-16 : 06:17:25

Try a function to see what happens.
Do a loop from 1 by 1 until var reaches datalength(your_column)
Inside loop check SUBSTRING(your_column,var, 1) on ASCII code and add 1 to the according countervar

return countvalues as table or as concatenated string

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2009-09-16 : 06:21:48

Have a look at this Madhivanan's blog.

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-17 : 05:06:28

quote:
Originally posted by waterduck

DECLARE @x VARCHAR(50)
SELECT @x = 'a534=-\aaaaaaaaa'

how to count all alpha/numeric/symbol in a column?

Hope can help...but advise to wait pros with confirmation...

Are you able to solve this?

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-17 : 09:52:55

he he....not quite free to handle 'extra' feature yet

Hope can help...but advise to wait pros with confirmation...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-17 : 09:59:32

quote:
Originally posted by waterduck

he he....not quite free to handle 'extra' feature yet

Hope can help...but advise to wait pros with confirmation...

So. in advance you ask questions and get the answers?

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-23 : 23:04:28

[code]DECLARE @fun TABLE (col1 VARCHAR(1000))
INSERT INTO @fun SELECT
'12345' UNION SELECT
'abcde' UNION SELECT
'!@#$%' UNION SELECT
' ' UNION SELECT
'12345abcde' UNION SELECT
'12345 bcde' UNION SELECT
123 @!# cde'

SELECT LEN(col1), col1 FROM @fun
SELECT DATALENGTH(col1), col1 FROM @fun[/code]
Eventually it doesn't need any looping...

Hope can help...but advise to wait pros with confirmation...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-24 : 02:21:54

Yes. As you see, LEN function omits trailing spaces

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources