Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Split string at uppercase 1

Status
Not open for further replies.

Linda224

Programmer
Dec 6, 2006
80
US
Hello
I have a column that has the description like this:
"ThisIsTheDescription"
Can that be selected to read:
"This Is The Description"

Any help would be apprieciated
Thank you
Linda
 
IMHO, you would need to create a UDF for this that will return a string based on a string you passed. In this UDF you would need to loop from 1 to the LEN(@String) and check for asci(substring(@String,i,1)) =asci(upper(substring(@String,i,1)) to find if this is an upper.

This is an idea only, but you should be able to put this into UDF now.
 
The only way I know is to code a loop to look for the ascii code of the letter, however I am no expert. Also to check the case of a letter you might need to get a collation that is case sensitive.

Good Luck,

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!
 
wow um ok I guess I am more of a beginner than I thought

What is a UDF and how do I do that?
 
UDF - user defined function
Look up CREATE FUNCTION

Please note that using a udf in a select my take some time.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!
 
would it just be easier to do this in the code behind c#
 
Define easier?

The real question is.... Where do you think the most appropriate place for this functionality should be.

In my opinion, it would be better to put this code in the c# code behind. You see, SQL is not so good at looping, and to accomplish this, ultimately loops will be involved. It may not be as bad as you think, though.

As an exercise, I found this interesting,so...

To create a User Defined Function, open a query window and execute this code:

Code:
Create Function dbo.SpaceUpper(@Original VarChar(8000))
Returns VarChar(8000)
As
Begin

  While PatIndex('%[^ ][A-Z]%', @Original Collate Latin1_General_Bin) > 0
	Set @Original = Replace(@Original Collate Latin1_General_Bin, SubString(@Original, 1+PatIndex('%[^ ][A-Z]%', @Original Collate Latin1_General_Bin), 1), ' '+ SubString(@Original, 1+PatIndex('%[^ ][A-Z]%', @Original Collate Latin1_General_Bin), 1))

  Return LTrim(@Original)
End

Then, you can call it like this...

Code:
Select dbo.SpaceUpper('ThisIsTheDescription')

or like this...
Code:
Select Column1,
       dbo.SpaceUpper(Column2) As ColumnX
From   YourTableName



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Well I could probably figure this out in the code behind but it is wanted in the sql.

I ran your code and it works beautifuly

Thank you so much!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top