SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Help needed to avoid cursor
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

sravz
Starting Member

USA
42 Posts

Posted - 04/14/2011 :  14:06:20  Show Profile  Reply with Quote
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 on

I 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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 04/14/2011 :  14:27:58  Show Profile  Reply with Quote
UPDATE yourTable
SET LinItem = RIGHT('0000'+linItem,4)

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

sravz
Starting Member

USA
42 Posts

Posted - 04/14/2011 :  15:05:56  Show Profile  Reply with Quote
I guess I need to explain the question clearly

Here is the eg table PoHistory
No LinItem
38999 00080
38777 00170
38514 1
38514 all----Any characters or empty found just put it to '0000'
38581 23

A 100 values in this table every week

I need to run the update on this table so that

the results of the table will be as follows
No LinItem
38999 0080
38777 0170
38514 0001
38514 0000
38581 0023

I had written the cursor to go through each linitem
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 04/14/2011 :  15:14:49  Show Profile  Reply with Quote
Somthing like this?
UPDATE yourTable
SET LineItem = CASE WHEN PATINDEX('%[^0-9]%',LineItem)>0 THEN '0000' ELSE RIGHT('0000'+LineItem,4) END

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

sravz
Starting Member

USA
42 Posts

Posted - 04/14/2011 :  15:24:34  Show Profile  Reply with Quote
Thanks for the immediate reply
Nope this is the error that SQL is giving


Argument data type decimal is invalid for argument 2 of patindex function.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 04/14/2011 :  15:33:21  Show Profile  Reply with Quote
Maybe a copy-paste error, it works for me.

DECLARE @Table Table(Num int, LineItem varchar(5),NewLineItem varchar(4))
INSERT INTO @Table
SELECT 38999,'00080',null UNION
SELECT 38777,'00170',null UNION
SELECT 38514,'1',null UNION
SELECT 38514,'all',null UNION
SELECT 38581, '23',null

UPDATE @Table
SET NEwLineItem = CASE WHEN PATINDEX('%[^0-9]%',LineItem)>0 THEN '0000' ELSE RIGHT('0000'+LineItem,4) END

SELECT * from @table

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

sravz
Starting Member

USA
42 Posts

Posted - 04/19/2011 :  10:21:35  Show Profile  Reply with Quote
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

Edited by - sravz on 04/19/2011 10:22:42
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 04/19/2011 :  10:30:42  Show Profile  Reply with Quote
UPDATE @Table
SET 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%'

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

sravz
Starting Member

USA
42 Posts

Posted - 04/19/2011 :  10:46:18  Show Profile  Reply with Quote
I mean the format can be any combination of letters and numbers in line item then do not update it else the Case statement

SET NewLineItem = CASE WHEN PATINDEX('%[^0-9]%',LineItem)> 0 THEN '0000' ELSE RIGHT('0000'+LineItem,4)

should work.


Thanks in Advance

Edited by - sravz on 04/19/2011 10:47:13
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 04/19/2011 :  10:56:59  Show Profile  Reply with Quote
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,

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

sravz
Starting Member

USA
42 Posts

Posted - 04/19/2011 :  11:33:49  Show Profile  Reply with Quote
Ok sure.Here is the example data

Here is the eg table PoHistory
No LinItem
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 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 here
38612 12A34----Any combination of letters and numbers found just leave them .No Update here too.



Thanks,
Sravz

Edited by - sravz on 04/19/2011 11:35:38
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 04/19/2011 :  11:53:34  Show Profile  Reply with Quote
DECLARE @Table Table(Num int, LineItem varchar(5),NewLineItem varchar(10))
INSERT INTO @Table
SELECT 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 here
SELECT 38612, '12A34',null ----Any combination of letters and numbers found just leave them .No Update here too.

UPDATE @Table
SET 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 @table

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

sravz
Starting Member

USA
42 Posts

Posted - 04/19/2011 :  13:48:44  Show Profile  Reply with Quote
Hey Jimf
Nope 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 here
38612 12A34----Any combination of letters and numbers found just leave them .No Update here too.


except the all data nothing else are changing

Thanks,
Sravanthi

Go to Top of Page

sravz
Starting Member

USA
42 Posts

Posted - 04/19/2011 :  14:19:26  Show Profile  Reply with Quote
UPDATE @Table
SET NewLineItem = CASE WHEN PATINDEX('%[^0-9]%',LineItem)> 0 THEN '0000' ELSE RIGHT('0000'+LineItem,4) END
WHERE ISNUMERIC(LineItem)=1

I 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 here
38612 12A34----Any combination of letters and numbers found just leave them .No Update here too.

the 'all' row needs to be updated.

Edited by - sravz on 04/19/2011 14:20:28
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 04/19/2011 :  17:49:03  Show Profile  Reply with Quote
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.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

sravz
Starting Member

USA
42 Posts

Posted - 04/20/2011 :  09:39:28  Show Profile  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 04/20/2011 :  10:01:49  Show Profile  Reply with Quote
You're welcome. Probably a copy/paste error again.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

sravz
Starting Member

USA
42 Posts

Posted - 04/26/2011 :  14:16:52  Show Profile  Reply with Quote
Hey Jim,

I got a new case for the LineItem.Eg:



No LinItem
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 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 here
38612 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

Edited by - sravz on 04/26/2011 14:20:14
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 04/26/2011 :  14:19:51  Show Profile  Reply with Quote
You really need to post the code that YOU have tried...Jim has spoon fed you enough

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 04/26/2011 :  15:10:34  Show Profile  Reply with Quote
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 @Table
SET 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 


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

sravz
Starting Member

USA
42 Posts

Posted - 04/26/2011 :  16:19:41  Show Profile  Reply with Quote
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.

Edited by - sravz on 04/26/2011 16:27:36
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000