Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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)
 replace characters on condition in table and cells
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

midan1
Starting Member

39 Posts

Posted - 04/14/2008 :  18:39:23  Show Profile  Reply with Quote
need help

replace characters on condition in table and cells

but only if

if i put number or 1 , 2 , 3 , 4 above the cell of the eployee for example( employee id=222 name =bbbb day1)

i replace characters with '0' and '#'

and it must work dynamically AND replace ONLY THIS characters


table before the replace
id fname val day1 day11 day111 day2 day22 day222
------------------------------------------------------
111 aaaa 2 1 3
111 aaaa 1 A C
222 bbbb 2
222 bbbb 1
333 cccc 2
333 cccc 1
444 dddd 2
444 dddd 1
555 eeee 2 2
555 eeee 1 B

table after the replace
id fname val day1 day11 day111 day2 day22 day222
------------------------------------------------------
111 aaaa 2 0 0
111 aaaa 1 # #
222 bbbb 2
222 bbbb 1
333 cccc 2
333 cccc 1
444 dddd 2
444 dddd 1
555 eeee 2 0
555 eeee 1 #

tnx FOR THE HELP

Edited by - midan1 on 04/14/2008 19:11:41

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 04/14/2008 :  18:45:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
UPDATE Table1
SET day1 = CASE WHEN day1 IS NULL THEN NULL WHEN day1 NOT LIKE '%[^0-9]%' THEN 0 ELSE '#' END,
day11 = CASE WHEN day11 IS NULL THEN NULL WHEN day11 NOT LIKE '%[^0-9]%' THEN 0 ELSE '#' END,
day111 = CASE WHEN day111 IS NULL THEN NULL WHEN day111 NOT LIKE '%[^0-9]%' THEN 0 ELSE '#' END,
day2 = CASE WHEN day2 IS NULL THEN NULL WHEN day2 NOT LIKE '%[^0-9]%' THEN 0 ELSE '#' END,
day22 = CASE WHEN day22 IS NULL THEN NULL WHEN day22 NOT LIKE '%[^0-9]%' THEN 0 ELSE '#' END,
day222 = CASE WHEN day222 IS NULL THEN NULL WHEN day222 NOT LIKE '%[^0-9]%' THEN 0 ELSE '#' END



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

midan1
Starting Member

39 Posts

Posted - 04/14/2008 :  18:53:26  Show Profile  Reply with Quote
tnx Peso
but i need only to replace
not update
how ?
tnx
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 04/14/2008 :  18:55:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Replace content of a "field" is done by UPDATE.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

midan1
Starting Member

39 Posts

Posted - 04/14/2008 :  19:06:51  Show Profile  Reply with Quote
but it must be depending on tow rows
OF THE EMPLOYEE
the employee have tow rows
val=1
val=2
not only replace the characters
only if i put number or 1 , 2 , 3 , 4 above the cell of the employee


ID fname val day1 day11 day111
---------------------------------
111 aaaa 2 1 3
111 aaaa 1 A C


like this condition not work !!!
because i don't HAVE number or 1 , 2 , 3 , 4 above THE cell

ID fname val day1 day11 day111
---------------------------------
111 aaaa 2
111 aaaa 1 A C


tnx


Edited by - midan1 on 04/14/2008 19:10:42
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 04/15/2008 :  03:14:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Please read and understand this blog post http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Then post again your problem.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
  Previous Topic Topic Next 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.06 seconds. Powered By: Snitz Forums 2000