3 Replies - 40597 Views - Last Post: 16 July 2007 - 06:42 AM Rate Topic: -----

#1 heqi0964  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 05-July 07

T-SQL question:how to count number of char in a text field

Posted 05 July 2007 - 09:42 PM

Question Details: Dear All,

how can i use t-sql to count the number of the ASCII character in a text field?

for example:
text: '123%Jimmy%Assigned a job% close the job'

I need to cout number of '%' in this text, so output for this example is 3


What is the code in T-SQL?


Many Thanks
Is This A Good Question/Topic? 0
  • +

#5 kenham40  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 14
  • Joined: 22-June 07

Re: T-SQL question:how to count number of char in a text field

Posted 06 July 2007 - 05:58 PM

View Postheqi0964, on 5 Jul, 2007 - 09:42 PM, said:

Question Details: Dear All,

how can i use t-sql to count the number of the ASCII character in a text field?

for example:
text: '123%Jimmy%Assigned a job% close the job'

I need to cout number of '%' in this text, so output for this example is 3


What is the code in T-SQL?


Many Thanks


As taken from: http://sqljunkies.co...spx?PostID=8514

Here's a link to a user-defined function that will return the number of times a certain character exists in an input string:

http://www.sql-serve...-character.aspx

To use it in you case, your query will look like this:

SELECT ResponseID, [dbo].[ufn_CountChar]( Answer, 'Y') AS Yes,
[dbo].[ufn_CountChar](Answer, 'N') AS No
FROM Questionnaire

http://www.sqljunkie...tSubstring.aspx

Here's a link that counts the number of times a search string exists in a given input string:

http://www.sql-serve...unt-string.aspx
Was This Post Helpful? 1

#6 bernard.assaf  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 1
  • View blog
  • Posts: 41
  • Joined: 15-July 07

Re: T-SQL question:how to count number of char in a text field

Posted 16 July 2007 - 12:24 AM

why using SQL to count the number of characters while you can read it in the programming code and manipulate it the way you wish. this can be done before inserting or after retrieving the data field.
Was This Post Helpful? 0
  • +
  • -

#7 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: T-SQL question:how to count number of char in a text field

Posted 16 July 2007 - 06:42 AM

There may be nor programming code per se, simply a database job being run. Also, it's often better to use functionality built into database software than bloat one's program with tasks that can be performed through the db with no overhead to the application itself. We often forget, as we try to make our own code modular and reusable, that other applications have been designed like this as well - not need to reinvent the wheel when the functionality already exists.
Was This Post Helpful? 1

Page 1 of 1