score:6

Accepted answer

The problem on your query is that replacing the * on your string makes it end with three trailing spaces, and LEN doesn't count those. So your result is 3. Try using DATALENGTH:

DECLARE @LongSentence VARCHAR(MAX)
DECLARE @FindSubString VARCHAR(MAX)
SET @LongSentence = 'Pravin Gaonkar: 29 Jan 2013 17:29:22 :  *'
SET @FindSubString = '*'

SELECT DATALENGTH(@LongSentence) - 
       DATALENGTH(REPLACE(@LongSentence,@FindSubString,''))[Count]

score:-2

This is Oracle query. I'm not sure if LEVEL or ROWNUM that can be used instead of LEVEL is available in your version of SQL. But it may still help you:

SELECT count(*) cnt FROM
(
 SELECT DISTINCT(Instr('Pravin Gaonkar: 29 Jan 2013 17:29:22 :  *', '*', LEVEL)) char_pos
   FROM dual
 CONNECT BY LEVEL <= Length('Pravin Gaonkar: 29 Jan 2013 17:29:22 :  *') 
)
WHERE char_pos > 0
/

SQL>

CNT
---
  1

SQL Server 2008 - Querying a Hierarchical Table Using Hierarchy Methods (GetRoot, and GetLevel...) - equivavelt to Oracle hierarchy used in my example: http://msdn.microsoft.com/en-us/library/3b4f7dae-65b5-4d8d-8641-87aba9aa692d(v=sql.100)


More questions

More questions with similar tag