You certainly don't need to loop through the string to search for some pattern. Just use CHARINDEX or PATINDEX. They return a value > 0 when a match is found....

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

See if this helps:

http://www.karaszi.com/SQLServer/info_datetime.asp

This will be another neverending story, I think.

It's fairly easy at the moment to write bad code for SQL Server. It will get even easier with CLR integration. I...

Farrell, nobody else but you can answer this question.

But I wanted to say something that hasn't been addressed yet. The new job perspective...

Doh, I've missed this "sometimes" and had thought that there is never a comma. Thanks for catching that!

I like to think that this is for data cleansing to get rid of 1NF violation

I'm going home now.

Happy Easter to you all!

If you can ensure the string *always* contains 'USA', this might work

declare @string varchar(20)

set @string = 'Chicago, IL USA'

select replace(rtrim(ltrim(@string)), ' USA',', USA')

-----------------------

Chicago, IL, USA

(1 row(s) affected)

Haha, for qualifying this as a shameless plug, you forgot to post the URL.

... and if that still isn't enough, and you know in advance that you need to keep a row for almost every single grain of sand in the Sahara, you can do...

Since each sample should be taken randomly, I assume, I think you must bite the bullet and write 4 queries SELECT ... BY NEWID(). Sorry, I don't know any other...

Does it need to be a UDF at all?

I did the google thing yesturday (forget my exact clause but it was similar) and I was just returning garbage as far as usefull sites.

Yes, it seems to be an...

TANSTAAFL.

Short answer, no. But please read the article I referenced

http://www.sommarskog.se/share_data.html