score:6
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
- Count occurrences of a character in sql
- How do you count the number of occurrences of a certain substring in a SQL varchar?
- SQL - count number of occurrences in a column
- Count max number of consecutive occurrences of a value in SQL Server
- SQL keeping count of occurrences through a sliding window
- How to count most consecutive occurrences of a value in a Column in SQL Server
- SQL Keeping count of occurrences
- Count number of occurrences in a bit column in sql
- Get count of multiple column occurrences in SQL
- How to count a character in SQL Server?
- SQL How to count the amount of same occurrences for every day
- SQL count occurrences rows
- Count # of occurrences in time span in SQL
- Running Count of Unique Identifier Occurrences in SQL
- Count number of occurrences per hour between two dates SQL Server
- SQL Server query - count occurrences of values in a column with specific criteria on multiple tables
- splitting a string and count occurrences sql server
- Count number of occurrences by the hour in SQL
- SQL count unique occurrences of gender
- SQL - count occurrences in a column by DAY
- How to fetch the row count for all tables in a SQL SERVER database
- Escape Character in SQL Server
- SQL Server: Maximum character length of object names
- SQL count rows in a table
- Get everything after and before certain character in SQL Server
- SQL Server replace, remove all after certain character
- SQL Server Group by Count of DateTime Per Hour?
- SQL Server default character encoding
- Count based on condition in SQL Server
- Count the Number of Tables in a SQL Server Database
More questions with similar tag
- ROW_NUMBER() function returns varchar value instead of int
- How to handle parallel bulk update requests in SQL Server?
- Bind variable options
- Code generation - generating DTOs by referencing database tables.
- how to use LIKE and % with pymssql on an undetermined number of parameters?
- Group and Ordering in SQL
- t-sql for json skip column names in a result
- A positional parameter cannot be found that accepts argument while importing csv file
- Using Smo in C# - skip constraint generating instructions at the beginning
- Truncation error when import excel file to Microsoft SQL
- SQL Server - Compare 2 tables for data existing in the same columns without checking for equality
- SSIS 2012 Project connection manager error
- SSIS Excel to SQL Server - Long Data on Column Failure
- How to sum one column from multiple
- Is there an easy way to remove all default values for a SQL database?
- SQL Server - Group Records Like Archives By Month
- xp_cmdshell using problem (SQL Server 2008 R2)
- SQL Server 2008 R2 connection string
- Creating two columns from consecutive dates of a column in T-SQL
- SQL OLEDB fails with TLS 1.2
- System.Data.SqlClient.SqlException after CREATE/ALTER/PRINT
- T-SQL - Dedup a string
- Inserting date from Access DB into SQL Server 2008R2
- Need to flatten column to a single row per group with a sum of the column values for each group
- Slow DELETE statement in SQL Server need help reading execution plan and fixing
- Query SQL distinct field with max date, returning all fields from table
- Looping RecordSet Destination in SSIS Script Task
- I need to understand the following MS SQL statement ( SELECT FROM VALUES)
- Azure API returning 204 No Content for Db-Calls with content
- Count of two columns while keeping the remaining columns