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 |
|
Claire_Toronto
Starting Member
11 Posts |
 Posted - 2011-08-08 : 09:40:47
|
| Hi!I have a string which can vary in length and content (50K rows) I need to scan the string and if the string has the text CRRW in it to call in CREDIT...the string may have underscores in it... numbers and letters so it can be complicated... I guess you could use a case statement?I hope someone could please help...Thank you!ClaireExample:DX_DFT_898G_20110405_CRRWAnswer: CREDITExample 2:DX_DFT_CRRW_898G_20110405_Answer:CREDIT |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2011-08-08 : 09:52:54
|
| If you mean replace the value, then:REPLACE(FieldName,'CCRW','CREDIT')If you mean setting another field, case with something like PATINDEX() or a LIKE on your where clause should do the trick. |
 |
|
|
Claire_Toronto
Starting Member
11 Posts |
Posted - 2011-08-08 : 10:26:50
|
| yes I read about patindex, I am trying to get it to work... can you please help with the syntax? I do need it in another field...thank you |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-08-08 : 11:40:08
|
| where patindex('%CRRW%','DX_DFT_CRRW_898G_20110405_') >0just change this 'DX_DFT_CRRW_898G_20110405_'to be the name of the column you're searching.JimEveryday I learn something that somebody else already knew |
|
|
|
Claire_Toronto
Starting Member
11 Posts |
Posted - 2011-08-08 : 12:31:34
|
| thank you its working. I am wondering how to make this a nested case, I need to check more values like if its ADJT then its ADJUSTED etc... how can I incorporate this here? , CASE WHEN PATINDEX('%CRRW', CLASSx) = 0 THEN NULL ELSE SUBSTRING(CLASSx, PATINDEX('%CRRW%', CLASSx), 4) END AS TYPE |
|
|
|
Claire_Toronto
Starting Member
11 Posts |
Posted - 2011-08-08 : 12:33:36
|
| sorry forgot to mention... it has to be in the same column "TYPE" but it doesn't have to say adjusted it can say ADJT |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2011-08-09 : 03:45:44
|
| Just add more WHEN clauses |
|
|
|
|
|
|
|
|
SQL String manipulation