Help needed to avoid cursor - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help needed to avoid cursor

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 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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-14 : 14:27:58

UPDATE yourTable
SET LinItem = RIGHT('0000'+linItem,4)

Jim

Everyday 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 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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-14 : 15:14:49

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

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 giving

Argument 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 @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

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 @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

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 statement

SET 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,

Jim

Everyday 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 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

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 @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

sravz
Starting Member

42 Posts

Posted - 2011-04-19 : 13:48:44

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

sravz
Starting Member

42 Posts

Posted - 2011-04-19 : 14:19:26

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.

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.

Jim

Everyday 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.

Jim

Everyday 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 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

X002548
Not Just a Number

15586 Posts

Posted - 2011-04-26 : 14:19:51

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/

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 @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

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.

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources