December 30, 2008 at 2:36 am
create table temp (id int identity(1,1),Name varchar(50))
insert into temp (name) select 'IP 20867 Corona'
insert into temp (name) select '19"-RAC - C60184-03'
insert into temp (name) select 'HEEFA Tower Hoist - 0368'
insert into temp (name) select 'MC - GIA - 0608 - Preload tank stairs'
insert into temp (name) select 'Palfinger - 0707 - montagevejledning'
I want to get the output which has starting character digit[0-9] continued by 4 or 5 digit.If it has any special
characters like ",-[a-z] it should not be include in the output.I wrote a query but it is including even the 19"-R which is
not required.The query I wrote is given below.Please help me
select patindex('%[0-9]%',Name),
substring(Name,patindex('%[0-9]%',Name),5),Name from temp
where (Name like '%[0-9][0-9][0-9][0-9]%'
or Name like '%[0-9][0-9][0-9][0-9][0-9]%')
drop table temp
December 30, 2008 at 5:54 am
Ashwin M N (12/30/2008)
create table temp (id int identity(1,1),Name varchar(50))insert into temp (name) select 'IP 20867 Corona'
insert into temp (name) select '19"-RAC - C60184-03'
insert into temp (name) select 'HEEFA Tower Hoist - 0368'
insert into temp (name) select 'MC - GIA - 0608 - Preload tank stairs'
insert into temp (name) select 'Palfinger - 0707 - montagevejledning'
I want to get the output which has starting character digit[0-9] continued by 4 or 5 digit.If it has any special
characters like ",-[a-z] it should not be include in the output.I wrote a query but it is including even the 19"-R which is
not required.The query I wrote is given below.Please help me
select patindex('%[0-9]%',Name),
substring(Name,patindex('%[0-9]%',Name),5),Name from temp
where (Name like '%[0-9][0-9][0-9][0-9]%'
or Name like '%[0-9][0-9][0-9][0-9][0-9]%')
drop table temp
Is this correct?
select data from
(
select '8762khsdf' as data union all
select 'ak034908sdf' union all
select '928374987'
) as t
where data like '[0-9][0-9][0-9][0-9][0-9]%' or data like '[0-9][0-9][0-9][0-9]%'
Failing to plan is Planning to fail
December 30, 2008 at 6:10 am
Even if I execute this query I cannot exclude the 19"-R character. The " character is main problem.For rest all my query works fine.Is there any way I can exclude the " character.I do not know why even after specifying [0-9] the " is included in the output.
December 30, 2008 at 6:55 am
Ashwin M N (12/30/2008)
Even if I execute this query I cannot exclude the 19"-R character. The " character is main problem.For rest all my query works fine.Is there any way I can exclude the " character.I do not know why even after specifying [0-9] the " is included in the output.
select data from
(
select '8762khsdf' as data union all
select 'ak034908sdf' union all
select '928374987' union all
select '19"-RAC - C60184-03'
) as t
where data like '[0-9][0-9][0-9][0-9][0-9]%' or data like '[0-9][0-9][0-9][0-9]%'
Failing to plan is Planning to fail
December 30, 2008 at 7:02 am
Ashwin M N (12/30/2008)
create table temp (id int identity(1,1),Name varchar(50))insert into temp (name) select 'IP 20867 Corona'
insert into temp (name) select '19"-RAC - C60184-03'
insert into temp (name) select 'HEEFA Tower Hoist - 0368'
insert into temp (name) select 'MC - GIA - 0608 - Preload tank stairs'
insert into temp (name) select 'Palfinger - 0707 - montagevejledning'
I want to get the output which has starting character digit[0-9] continued by 4 or 5 digit.If it has any special
characters like ",-[a-z] it should not be include in the output.I wrote a query but it is including even the 19"-R which is
not required.The query I wrote is given below.Please help me
select patindex('%[0-9]%',Name),
substring(Name,patindex('%[0-9]%',Name),5),Name from temp
where (Name like '%[0-9][0-9][0-9][0-9]%'
or Name like '%[0-9][0-9][0-9][0-9][0-9]%')
drop table temp
The problem is because you are using the patindex function and you look for an occurrence of only 1 digit. The line 19"-RAC - C60184-03 begins with a digit, but it is not what you are looking for. You are looking for the part that has 4 consecetive digits. You need to modify the part with the patindex, so it will look for 4 consecetive digits instead of the first digit that it finds. You can also change the where clause. Currently you are looking for any string that has at least 4 or 5 consecetive digits. If you’ll ommit the part that is looking for 5 consecetive digits, you’ll get the same results because of the part that is looking for 4 consecetive digits. Bellow is my version of your query:
select patindex('%[0-9][0-9][0-9[0-9]%',Name),
substring(Name,patindex('%[0-9][0-9][0-9[0-9]%',Name),5),Name
from temp
where Name like '%[0-9][0-9][0-9][0-9]%'
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 30, 2008 at 10:43 am
Madhivanan (12/30/2008)
Ashwin M N (12/30/2008)
Even if I execute this query I cannot exclude the 19"-R character. The " character is main problem.For rest all my query works fine.Is there any way I can exclude the " character.I do not know why even after specifying [0-9] the " is included in the output.
select data from
(
select '8762khsdf' as data union all
select 'ak034908sdf' union all
select '928374987' union all
select '19"-RAC - C60184-03'
) as t
where data like '[0-9][0-9][0-9][0-9][0-9]%' or data like '[0-9][0-9][0-9][0-9]%'
Agreed....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply