March 12, 2009 at 10:12 am
for my code below i want to check weather name is starting from p_abc_ or not??
where abc = position 3,4 and 5 and they must be alphabets no numbers
i m not getting this because my like condition is not true..
can anyione tell me how to wtite like condition for this requirement?
Thanks
DECLARE @data XML;
DECLARE @Name sysname;
DECLARE @cmd sysname;
declare @sql varchar(100),@line varchar(100)
set @Name = 'p_a2e_webster' --p_amd_webster
--SET @data = EVENTDATA();
--SET @Name= @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
set @sql= ('select substring('''+@Name+''',3,1)')
--Exec(@sql)
--print @sql
set @cmd = ('select substring('''+@Name+''',3,1)')
--exec (@cmd)
set @line = ('select substring('''+@Name+''',3,1)')
--exec (@line)
set @name =
(Case
when
(@sql like '%[a-zA-Z]%' and @cmd like '%[a-zA-Z]%' and @line like '%[a-zA-Z]%')
Then 'select ''p_''+substring('''+@Name+''',3,3)+''_%'''
When
(@sql not like '%[a-zA-Z]%' and @cmd not like '%[a-zA-Z]%' and @line not like '%[a-zA-Z]%')
Then 'position 3-5 alpha'
Else '0'
ENd)
EXEC(@name)
print @name
March 12, 2009 at 10:32 am
March 12, 2009 at 11:45 am
Hi
Maybe I did not understand correct, but try this:
DECLARE @name VARCHAR(100)
SET @name = 'xxxabcxxx'
IF (@name LIKE '___abc%')
PRINT (@name + ' matches - RIGHT')
SET @name = 'abcab'
IF (@name LIKE '___abc%')
PRINT (@name + ' matches - STRANGE!')
Greets
Flo
March 12, 2009 at 12:02 pm
--ok i have modified it but still its not giving coreect results
i wan 3, 4 and 5 th position as only alphabets no number or anything else..
but its not checking properly that means like condition is not peoper here..
DECLARE @Temp varchar(200);
DECLARE @Name sysname;
DECLARE @cmd sysname;
declare @sql varchar(100),@line varchar(100)
set @Name = 'p_a3e_webster' --p_amd_webster
--SET @data = EVENTDATA();
--SET @Name= @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
set @sql= ('select substring('''+@Name+''',3,1)')
Exec(@sql)
print @sql
set @cmd = ('select substring('''+@Name+''',4,1)')
exec (@cmd)
print @sql
set @line = ('select substring('''+@Name+''',5,1)')
exec (@line)
print @sql
IF @sql like '%[a-z]%' and @cmd like '%[a-z]%' and @line like '%[a-z]%'
Begin
set @Temp = 'select ''p_''+substring('''+@Name+''',3,3)+''_%'''
EXEC (@Temp)
print @Temp
End
ELSE
IF @sql not like '%[a-z]%' or @cmd not like '%[a-z]%' or @line not like '%[a-z]%'
Begin
Print ('position 3-5 alphabets')
End
March 12, 2009 at 12:13 pm
Oh sorry. Did not read correct. Try this:
DECLARE @name VARCHAR(100)
SET @name = '123abc123'
IF (@name LIKE '___[a-z][a-z][a-z]%')
PRINT (@name + ' matches - RIGHT')
SET @name = '123a2b345'
IF (@name LIKE '___abc%')
PRINT (@name + ' matches - STRANGE!')
Greets
Flo
March 12, 2009 at 12:25 pm
well i think this is not wht i want.. or m not getting u..
just copy n paste my code on ur ssms query window and execute it.. u willl understand there...
March 12, 2009 at 12:43 pm
I am quiet sure that I understood you. Let me try to explain what I understood:
You want to check if the third to fifth position within a specified text are alphabets (no numbers, no other characters).
Some example data and the requested result, supposed by me:
abcdefghi TRUE
123abc234 TRUE
abc2a2abc FALSE
123ab5def FALSE
__a_e_fgh FALSE
___abcdef TRUE
So if I'm right try the values in the sample:
DECLARE @name VARCHAR(100)
SET @name = 'abcdefghi'
IF (@name LIKE '___[a-z][a-z][a-z]%')
PRINT (@name + ' matches')
ELSE
PRINT (@name + ' does not match')
March 12, 2009 at 12:54 pm
Here is some test code. Let me know if this helps.
declare @teststr varchar(20);
set @teststr = 'p_a3c_master';
select
@teststr
where
@teststr like '__[A-Za-z][A-Za-z][A-Za-z]%';
set @teststr = 'p_abc_master';
select
@teststr
where
@teststr like '__[A-Za-z][A-Za-z][A-Za-z]%';
March 12, 2009 at 1:37 pm
well ya thts fine.. thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply