Author |
Topic |
sravz
Starting Member
42 Posts |
Posted - 2011-04-14 : 14:06:20
|
I had a table name pohistory Which had a column name LinItem ---Varchar(5).The user saves the value as 00090,00109,1,3,all so on I need to check each LinItem and update it like this 0090,0109,0001,0003,0000 so onI wrote the query using Cursor .But I want to avoid cursor.If so whats the best way to code it.Please help me .Thanks In Advance. |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-04-14 : 14:27:58
|
UPDATE yourTableSET LinItem = RIGHT('0000'+linItem,4)JimEveryday I learn something that somebody else already knew |
|
|
sravz
Starting Member
42 Posts |
Posted - 2011-04-14 : 15:05:56
|
I guess I need to explain the question clearlyHere is the eg table PoHistoryNo LinItem38999 0008038777 0017038514 138514 all----Any characters or empty found just put it to '0000'38581 23A 100 values in this table every weekI need to run the update on this table so thatthe results of the table will be as follows No LinItem38999 008038777 017038514 000138514 000038581 0023I had written the cursor to go through each linitem |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-04-14 : 15:14:49
|
Somthing like this?UPDATE yourTableSET LineItem = CASE WHEN PATINDEX('%[^0-9]%',LineItem)>0 THEN '0000' ELSE RIGHT('0000'+LineItem,4) ENDJimEveryday I learn something that somebody else already knew |
|
|
sravz
Starting Member
42 Posts |
Posted - 2011-04-14 : 15:24:34
|
Thanks for the immediate reply Nope this is the error that SQL is givingArgument data type decimal is invalid for argument 2 of patindex function. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-04-14 : 15:33:21
|
Maybe a copy-paste error, it works for me.DECLARE @Table Table(Num int, LineItem varchar(5),NewLineItem varchar(4))INSERT INTO @TableSELECT 38999,'00080',null UNION SELECT 38777,'00170',null UNION SELECT 38514,'1',null UNION SELECT 38514,'all',null UNION SELECT 38581, '23',nullUPDATE @TableSET NEwLineItem = CASE WHEN PATINDEX('%[^0-9]%',LineItem)>0 THEN '0000' ELSE RIGHT('0000'+LineItem,4) END SELECT * from @tableJimEveryday I learn something that somebody else already knew |
|
|
sravz
Starting Member
42 Posts |
Posted - 2011-04-19 : 10:21:35
|
Hey Jimf,Thank you for the help,It worked out.But I need an exception in one case if LineItem has data like this 'all12' or '12A' or '123all' then I want to Ignore that case without updating data.Is there any possibility for that.Thanks,sravz |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-04-19 : 10:30:42
|
UPDATE @TableSET NewLineItem = CASE WHEN PATINDEX('%[^0-9]%',LineItem)> 0 THEN '0000' ELSE RIGHT('0000'+LineItem,4) END WHERE LineItem not like '%all12%' and LineItem not like '%12A%' and LineItem not like '%123all%'JimEveryday I learn something that somebody else already knew |
|
|
sravz
Starting Member
42 Posts |
Posted - 2011-04-19 : 10:46:18
|
I mean the format can be any combination of letters and numbers in line item then do not update it else the Case statementSET NewLineItem = CASE WHEN PATINDEX('%[^0-9]%',LineItem)> 0 THEN '0000' ELSE RIGHT('0000'+LineItem,4)should work.Thanks in Advance |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-04-19 : 10:56:59
|
I'm getting confused. I give you exactly what you ask for, and then you clarify what you really want. Could we start from the beginning and you can tell me exactly what you want, with new sample data and expected output?Thanks,JimEveryday I learn something that somebody else already knew |
|
|
sravz
Starting Member
42 Posts |
Posted - 2011-04-19 : 11:33:49
|
Ok sure.Here is the example dataHere is the eg table PoHistoryNo LinItem38999 00080----in this case result should be(0080)38777 00170----in this case result should be(0170)38514 1--------in this case result should be(0001)38514 all------Any characters(just characters) or empty string found just put it to '0000'38581 23-------in this case result should be(0023)38511 A123-----Any combination of letters and numbers found just leave them .No Update here38612 12A34----Any combination of letters and numbers found just leave them .No Update here too.Thanks,Sravz |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-04-19 : 11:53:34
|
DECLARE @Table Table(Num int, LineItem varchar(5),NewLineItem varchar(10))INSERT INTO @TableSELECT 38999,'00080',null UNION ----in this case result should be(0080)SELECT 38777,'00170',null UNION ----in this case result should be(0170)SELECT 38514,'1',null UNION --------in this case result should be(0001)SELECT 38514,'all',null UNION --Any characters(just characters) or empty string found just put it to '0000'SELECT 38581, '23',null UNION -------in this case result should be(0023)SELECT 38511, 'A123',null UNION-----Any combination of letters and numbers found just leave them .No Update hereSELECT 38612, '12A34',null ----Any combination of letters and numbers found just leave them .No Update here too.UPDATE @TableSET NEwLineItem = CASE WHEN PATINDEX('%[0-9]%',LineItem)>0 and PATINDEX('%[A-Z]%',LineItem)=0 THEN RIGHT('0000'+LineItem,4) WHEN PATINDEX('%[0-9]%',LineItem)=0 and PATINDEX('%[A-Z]%',LineItem)>0 THEN '0000' ELSE LineItem END SELECT * from @tableJimEveryday I learn something that somebody else already knew |
|
|
sravz
Starting Member
42 Posts |
Posted - 2011-04-19 : 13:48:44
|
Hey JimfNope its not working out .nothing in the result set changed .the result set after i run the query is 38999 00080----in this case result should be(0080)38777 00170----in this case result should be(0170)38514 1--------in this case result should be(0001)38514 0000------Any characters(just characters) or empty string found just put it to '0000'38581 23-------in this case result should be(0023)38511 A123-----Any combination of letters and numbers found just leave them .No Update here38612 12A34----Any combination of letters and numbers found just leave them .No Update here too.except the all data nothing else are changingThanks,Sravanthi |
|
|
sravz
Starting Member
42 Posts |
Posted - 2011-04-19 : 14:19:26
|
UPDATE @TableSET NewLineItem = CASE WHEN PATINDEX('%[^0-9]%',LineItem)> 0 THEN '0000' ELSE RIGHT('0000'+LineItem,4) ENDWHERE ISNUMERIC(LineItem)=1I tried it like this it works like this 38999 0080----in this case result should be(0080)38777 0170----in this case result should be(0170)38514 0001--------in this case result should be(0001)38514 all------Any characters(just characters) or empty string found just put it to '0000'38581 0023-------in this case result should be(0023)38511 A123-----Any combination of letters and numbers found just leave them .No Update here38612 12A34----Any combination of letters and numbers found just leave them .No Update here too.the 'all' row needs to be updated. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-04-19 : 17:49:03
|
Try the solution I gave you 04/19/2011:11:53:34 since it works and yours doesn't. Also, for your solution, add '1e7' to your sample data and try again. JimEveryday I learn something that somebody else already knew |
|
|
sravz
Starting Member
42 Posts |
Posted - 2011-04-20 : 09:39:28
|
Hey Jim ,It worked today.I dont know y it did not worked yesterday. I tried it for 10 times yesterday ....But today I tried it once it worked good.Thanks for the help.Thanks,Sravanthi |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-04-20 : 10:01:49
|
You're welcome. Probably a copy/paste error again.JimEveryday I learn something that somebody else already knew |
|
|
sravz
Starting Member
42 Posts |
Posted - 2011-04-26 : 14:16:52
|
Hey Jim,I got a new case for the LineItem.Eg:No LinItem38999 00080----in this case result should be(0080)38777 00170----in this case result should be(0170)38514 1--------in this case result should be(0001)38514 all------Any characters(just characters) or empty string found just put it to '0000'38581 23-------in this case result should be(0023)38511 A123-----Any combination of letters and numbers found just leave them .No Update here38612 12A34----Any combination of letters and numbers found just leave them .No Update here too.386876 4.01-----Any decimals need to be updated to '0000'Could u please help me.Thanks sravz |
|
|
X002548
Not Just a Number
15586 Posts |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-04-26 : 15:10:34
|
Here you are, sravz, but as Brett said, you should try it yourself first and then post it here , no matter how much it doesn't work, it's the only way the light bulbs ever go off. UPDATE @TableSET NEwLineItem = CASE WHEN PATINDEX('%[0-9]%',LineItem)>0 and PATINDEX('%[A-Z]%',LineItem)=0 and charindex('.',lineitem) = 0 THEN RIGHT('0000'+LineItem,4) WHEN ( PATINDEX('%[0-9]%',LineItem)=0 and PATINDEX('%[A-Z]%',LineItem)>0 ) or charindex('.',lineitem) > 0 THEN '0000' ELSE LineItem END JimEveryday I learn something that somebody else already knew |
|
|
sravz
Starting Member
42 Posts |
Posted - 2011-04-26 : 16:19:41
|
You are right I know that.I need it immediately in my place.That is the reason I posted it.Any ways thanks for the query. |
|
|
Next Page
|