How to count values within a string

1

I have a staff table and it has a field staff_id a string of 8.
I would like to run a query that will confirm that there are a total of 8 characters in the string. Because when some were entered, they did a copy/paste from excel, and this would drop the leading zeros.
So I need to report on which id's are lacking the totality of 8 characters.
Select staff_id, last_name, first_name where
** something like staff_id(1) is not blank
staff_id(2) is not blank
staff_id(3) is not blank
etc...4-8.

3

if it's about dropped leading zero's : something like
... where len(staff_id)< 8

?

4

hi

hope this helps

create sample data

drop table if exists #Staff

create table #Staff(Staff_ID varchar(8))

insert into #Staff select ' 4567'
insert into #Staff select ' 789065'
insert into #Staff select ' 908'
insert into #Staff select ' 4561235'
insert into #Staff select '12345678'
insert into #Staff select '34217856'

1st way using translate

select 
    * 
from 
   #Staff 
where  
   len(REPLACE(TRANSLATE(Staff_ID,'        ', '@@@@@@@@'), '@', '')) <> 8

2nd way using cast as int

select 
    * 
from 
   #Staff 
where  
   len(cast(staff_id as int))  <> 8

image

5

Staff_id < 8 is not going to work.
I want to create an array so that the staff_id would move to staff_area.
then look at each element of the array in a loop or one by one and see that there is a value. I am not sure how else one would do this.

6

please see my modified answers above

7

It's a fantastic idea I hope it can work.
I got this error: Msg 529, Level 16, State 2, Line 6
Explicit conversion from data type uniqueidentifier to int is not allowed.

select
*
from
Staff_view
where
len(cast(staff_id as int)) <> 8

8

hi

could you please post sample data

Provide Sample Data DDL

9

You say:

The len function does just this: return the lenght of a string, so I can't see why you would convert it to an int before checking, or use loops. SQL server doesn't like loops, it's the slowest and most expensive way to get things done

Later you mention you get the error

This points to the fact that the field staff_id contains values like
'39E8E5CF-5B07-4A43-ADC5-0AD73A912F66'

Could you please give us the correct field type of staff_id together with some sample data?

1 Like
10 

Select staff_id, last_name, first_name,
    stuff(
    case when substring(staff_id, 1, 1) = ' ' then ', char 1' else '' end +
    case when substring(staff_id, 2, 1) = ' ' then ', char 2' else '' end +
    case when substring(staff_id, 3, 1) = ' ' then ', char 3' else '' end +
    case when substring(staff_id, 4, 1) = ' ' then ', char 4' else '' end +
    case when substring(staff_id, 5, 1) = ' ' then ', char 5' else '' end +
    case when substring(staff_id, 6, 1) = ' ' then ', char 6' else '' end +
    case when substring(staff_id, 7, 1) = ' ' then ', char 7' else '' end +
    case when substring(staff_id, 8, 1) = ' ' then ', char 8' else '' end 
    , 1, 1, '') as which_chars_are_blank
from dbo.table_name
where
    len(replace(staff_id, ' ', '')) < 8
11

Yeah, this seems to be the best answer for the question asked. I don't quite get the array thing, it would be great if the OP would provide some sample rows and what they are trying to check for...

1 Like