CODEWITHSUNDEEP
mysqlsql
Prifulnath
Prifulnath

Reputation: 577

Finding count of unique value before a character

I have a some entries in database table rows as follows.

 101 - 1
 101 - 2
 101 - 3
 102 - 1
 102 - 2
 102 - 3
 103

I need to get the result of SELECT Query for count as '3' since there are 101 and 102 are the only number before the -.

So is there any way to find the unique value in db table columns before a character?

EDIT : I have entries even without the - .

Upvotes: 0

Views: 91

Answers (2)

720p Download Watch Dabangg 3
720p Download Watch Dabangg 3

Reputation: 205

create table T1
(
    id int primary key identity,
    col1 varchar(20)
)

insert into T1 values('101 - 1'),('101 - 2'),('101 - 3'),('102 - 1'),('102 - 2'),('102 - 3')

select  SUBSTRING(col1,0,CHARINDEX(' ',col1)) as 'Value',count(*) as 'Count' from T1 group by SUBSTRING(col1,0,CHARINDEX(' ',col1))

Upvotes: 1

jambonick
jambonick

Reputation: 716

In case your entries have always the format you have provided us, you just have to find the position of the '-' character, split the values, get the first n characters and count the distinct values

This works for SQL Server, otherwise informs us about what DBMS you are using or replace the functions with the ones of your DBMS on your own 

SELECT COUNT(DISTINCT SUBSTRING(val,0,CHARINDEX('-', val))) from YourTable

Upvotes: 2

Related Questions