Find the frequency of a char(s) in a string

jdang67 · October 13th, 2004, 02:01 PM

This is a question I could not anwser during the interview. Please help. I want to lean.

How to write a single sql statement that return the frequency of some character(s) in a column? For example, a description of a record is: "Wireless-G is the upcoming 54Mbps wireless networking standard ..." and The interviewer want to know how many 'es' in that string, the frequency of 'es' for each rows( can not use cursor).

Thanks

Imar · October 13th, 2004, 02:37 PM

I think this has been brought a couple of times on this forum, but if it's for an interview, don't you think it's better you find that out yourself?

What happens if you get the job, and on your first work day your boss ask a similar but slightly different question. Then what? What do you answer him?

I think honesty is best for the long term. If you don't know the answer, say so. It's not that bad if you don't know all the answers. If you're afraid of too many unanswered questions, you're applying for the wrong job ;)

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Jane Jane by Golden earring (Track 6 from the album: The hole) What's This?

jdang67 · October 13th, 2004, 02:44 PM

Yes I did tell him that I dont know, but I want to lean how to do that.

Imar · October 13th, 2004, 02:56 PM

Well, in that case, tell him there are two "es"'s in that text ;)

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Peter Rauhofer's Doomsday Club by Frankie Goes To Hollywood What's This?

Imar · October 13th, 2004, 03:34 PM

Just kidding.

Anyway, I am not sure if that can be done with pure T-SQL. Well, actually, it might be possible, I just don't know how to do it.

I think personally, if I had to do something like this, I'd write a little function that parses the string for the requested characters. This may not be the most efficient way, but it's the only way I know how to do it. (If it has to be done in SQL).

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Vanishing by A Perfect Circle (Track 5 from the album: Thirteenth Step) What's This?

jdang67 · October 13th, 2004, 03:50 PM

I think the same way. He also asked question like: "After deleted records with sql statement in T_SQL environment, and you commited the update, then you recorgnized you deleted the wrong records and He want to rollback( No backup database ). I didnot know the answer too, I failed the interview.

happygv · October 14th, 2004, 01:52 AM

AFAIK, one cannot rollback that once commit has happened.

_________________________
- Vijay G
Strive for Perfection

pgtips · October 14th, 2004, 02:41 AM

LEN(@s) - LEN(REPLACE(@s, 'e', ''))

do I get the job? :D

happygv · October 14th, 2004, 02:47 AM

Code:

select LEN('testtesttest') - LEN(REPLACE('testtesttest', 'es', ''))

Gives me 6. As per my understanding that should result in 3;). Though a great try. That works perfect for just one character.

Code:

select LEN('testtesttest') - LEN(REPLACE('testtesttest', 'e', ''))

Cheers!

_________________________
- Vijay G
Strive for Perfection