Author |
Topic |
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2012-12-20 : 07:19:51
|
hello all,i am trying this from morning but not able to understand is this scenario possible or not ???i have a table names usersnames stnameAB001 XYZ2AB001 XYZAB002 AAA2AB002 AAAhere i need to delete exclusively the records which starts with 2 like 2AB001 in this table is this possible to delete like this one with in the row need to pick first character and delete ??suggest meP.V.P.MOhan |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-20 : 07:22:10
|
Run this code to see what you will be deleting.SELECT * FROM tbl WHERE names like '2%' If you are satisfied that those indeed are the rows you want to delete, then do delete like this:DELETE FROM tbl WHERE names like '2%' |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2012-12-20 : 07:49:26
|
hey sunitha and chandu i tried with above queries there showing the same thing not deleting anything...declare @frogs table (names Varchar(100),stname varchar(10))Insert into @frogs values ('AB001','XYZ')Insert into @frogs values ('2AB001','XYZ')Insert into @frogs values ('AB002','AAA')Insert into @frogs values ('2AB002','AAA')select * from @frogsdelete from @frogs where LEFT(names,1) = '2'delete from @frogs where names like '2%'P.V.P.MOhan |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-20 : 08:01:22
|
declare @frogs table (names Varchar(100),stname varchar(10))Insert into @frogs values ('AB001','XYZ')Insert into @frogs values ('2AB001','XYZ')Insert into @frogs values ('AB002','AAA')Insert into @frogs values ('2AB002','AAA')select * from @frogsdelete from @frogs where LEFT(names,1) = '2'delete from @frogs where names like '2%'SELECT * FROM @frogsGONow execute and see resultsEDIT: In your code, you are selecting results before DELETE operation. thats why u didn't see difference--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-20 : 08:04:43
|
quote: Originally posted by mohan123 hey sunitha and chandu i tried with above queries there showing the same thing not deleting anything...declare @frogs table (names Varchar(100),stname varchar(10))Insert into @frogs values ('AB001','XYZ')Insert into @frogs values ('2AB001','XYZ')Insert into @frogs values ('AB002','AAA')Insert into @frogs values ('2AB002','AAA')select * from @frogsdelete from @frogs where LEFT(names,1) = '2'delete from @frogs where names like '2%'P.V.P.MOhan
works fine for medeclare @frogs table (names Varchar(100),stname varchar(10))Insert into @frogs values ('AB001','XYZ')Insert into @frogs values ('2AB001','XYZ')Insert into @frogs values ('AB002','AAA')Insert into @frogs values ('2AB002','AAA')select * from @frogs delete from @frogs where LEFT(names,1) = '2'delete from @frogs where names like '2%'select * from @frogsoutput--------------------------------beforenames stname-------------------------AB001 XYZ2AB001 XYZAB002 AAA2AB002 AAAafternames stname---------------------AB001 XYZAB002 AAA ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-20 : 08:06:09
|
it may be that you've unprintable characters like spaces in data. try this toodeclare @frogs table (names Varchar(100),stname varchar(10))Insert into @frogs values ('AB001','XYZ')Insert into @frogs values ('2AB001','XYZ')Insert into @frogs values ('AB002','AAA')Insert into @frogs values ('2AB002','AAA')select * from @frogs delete from @frogs where LEFT(LTRIM(names),1) = '2'delete from @frogs where LTRIM(names) like '2%'select * from @frogs ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2012-12-20 : 08:19:41
|
yeah i did a silly mistake. i thought those are 2 statements and seperated them and executed singletime thats the reason why i am wrong....sorry for nagging u guys with my sillynessP.V.P.MOhan |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-20 : 08:21:42
|
np... Glad that you sorted it out!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|