Forums
This topic is locked
Changing Field Data
Posted 19 Jul 2002 06:59:03
1
has voted
19 Jul 2002 06:59:03 carolus Holman posted:
Is it possible in SQL 2000 to take a text string like, "Paulsons & Johnson's" and create a new string, "PAUJOH". This is taking the first three letters of the first and second word. Each entry would contain different words (company Names). I am trying to build an ID based on this scheme to talk to an older database. I am using SQL2000, I haven't found anything that can Parse out non Alpha-Numeric, spaces, concatenate the string etc. I have tried replace, RTrim, LTrim. Any ideas out there.Carolus Holman
Replies
Replied 19 Jul 2002 17:09:12
19 Jul 2002 17:09:12 Viktor Farcic replied:
Use split to create array:
<pre id=code><font face=courier size=2 id=code>arrString = Split(strOldString, " "
</font id=code></pre id=code>
The create new string by combining first two letter of each array item:
<pre id=code><font face=courier size=2 id=code>For i = 0 To UBound(arrString)
If (arrString(i) <> "&" And (Len(arrString(i)) > 3) Then
strNewString = strNewString & Left(arrString(i))
ElseIf (arrString(i) <> "&" Then
strNewString = strNewString & arrString(i)
End If
Next
Response.Write(strNewString)</font id=code></pre id=code>
I havent' tested this so there can be some errors in the code above.
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Is it possible in SQL 2000 to take a text string like, "Paulsons & Johnson's" and create a new string, "PAUJOH". This is taking the first three letters of the first and second word. Each entry would contain different words (company Names). I am trying to build an ID based on this scheme to talk to an older database. I am using SQL2000, I haven't found anything that can Parse out non Alpha-Numeric, spaces, concatenate the string etc. I have tried replace, RTrim, LTrim. Any ideas out there.
Carolus Holman
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
Viktor Farcic
www.farcic.com
TalkZone Manager
<pre id=code><font face=courier size=2 id=code>arrString = Split(strOldString, " "
</font id=code></pre id=code>The create new string by combining first two letter of each array item:
<pre id=code><font face=courier size=2 id=code>For i = 0 To UBound(arrString)
If (arrString(i) <> "&"
And (Len(arrString(i)) > 3) ThenstrNewString = strNewString & Left(arrString(i))
ElseIf (arrString(i) <> "&"
ThenstrNewString = strNewString & arrString(i)
End If
Next
Response.Write(strNewString)</font id=code></pre id=code>
I havent' tested this so there can be some errors in the code above.
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Is it possible in SQL 2000 to take a text string like, "Paulsons & Johnson's" and create a new string, "PAUJOH". This is taking the first three letters of the first and second word. Each entry would contain different words (company Names). I am trying to build an ID based on this scheme to talk to an older database. I am using SQL2000, I haven't found anything that can Parse out non Alpha-Numeric, spaces, concatenate the string etc. I have tried replace, RTrim, LTrim. Any ideas out there.
Carolus Holman
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
Viktor Farcic
www.farcic.com
TalkZone Manager
Replied 23 Jul 2002 15:01:39
23 Jul 2002 15:01:39 carolus Holman replied:
I think this would work in ASP but it's definitely not going to work in SQL server. I was looking for SQL2000 T-SQL commands, and expressions. Alas I may have to do it in Classic ASP, because I suck at .NET!
Carolus Holman
Carolus Holman