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.
| Author |
Topic |
|
WJHamel
Aged Yak Warrior
651 Posts |
 Posted - 2012-04-05 : 11:14:01
|
i need to populate the County column in my GCSOSubjects table with a substring of the COURTNAME field in that same table. The values in COURTNAME are all formatted as "COUNTY-LEON" or "COUNTY-GADSDEN", or "CIRCUIT-GADSDEN" or "COUNTY-GADSDEN". The data is always either CIRCUIT-something or COUNTY-something.My instinct (as poor as it is) is to populate COUNTY using a substring with a where / like clause, but i'm not sure about the values for the substring. For instance, using:Update GCSOSUBJECTS set COUNTY=SUBSTRING(COURTNAME,8,?) where COURTNAME LIKE 'COUNTY-%'Update GCSOSUBJECTS set COUNTY=SUBSTRING(COURTNAME,9,?) where COURNTNAME LIKE 'CIRCUIT-%' My uncertainty is the second numerical value of the substring for each update.Of course, i'm sure someone is going to tell me there is a completely "other" way to do this not involving anything i'm attempting here. At any rate, anything that works.thanksjames |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-04-05 : 11:19:21
|
| Sorry, and "concatenate" should not have been in the title of this one. That was from another issue rolling around in my brainpan. |
 |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-04-05 : 11:35:26
|
| Disregard. figured it out. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-05 : 11:45:39
|
| can you post solutions for others understanding?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-04-05 : 12:25:19
|
Basically, in using this process, i used a numerical value for the second part of the substring which was longer than the len of any of the rows in that table. So my queries wound up looking like:Update GCSOSUBJECTS set COUNTY=SUBSTRING(COURTNAME,8,20) where COURTNAME LIKE 'COUNTY-%'Update GCSOSUBJECTS set COUNTY=SUBSTRING(COURTNAME,9,20) where COURNTNAME LIKE 'CIRCUIT-%' |
|
|
|
|
|
|
|
|
Substring and concatenate