pick up first character of every word in string punctuated by delimiter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

pick up first character of every word in string punctuated by delimiter

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-09-02 : 06:55:44

rajdeep Khanna writes "if there is string for example 'cricket; baseball; football; tennis'
I want to pick up c; b; f; t only.And if there are three words in string for examlep 'cricket; baseball; tennis'
it should choose c; b; t

Can any sql guru help with this immediately????
Thanks

Raj"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-02 : 10:28:16

for one, in the future, try normalizing your data and not storing lists of values in 1 column.

What do you want to return? a set of results, or 1 string of results?

if it's 1 string, just write a user-defined function. Loop through the string, take the first letter A-Z, and then after that take the first letter immediately following the semi-colons the rest of the way.

pseudo-code:


result = ''    -- what you are returning
pos=1          -- current position in the string
Semi-Colon= 1  -- semi-colon flag; 1 or 0

while pos <= len(StringToParse)
  Letter = mid(stringToParse,pos,1)
  if Letter like '[a-z]'
    if SemiColon = 1 
      Result = Result + '; ' + Letter
    end if
    SemiColon = 0
  end if
  if Letter = ';'
    SemiColon = 1
  Else
    SemiColon = 0
  End if

  Pos = Pos + 1
Loop

Result = mid(result,3)

something like that ... obviously not tested

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources