| Author |
Topic |
|
girishkardam
Starting Member
13 Posts |
 Posted - 2008-04-02 : 05:45:22
|
| Hi,I am using SQL Server 2000. In database i am having one column named Address which contains full address of the customer. While searching i want to ignore starting numeric or alphanumeric values. Kinly guide how I can ignore numeric or alphanumeric values while searching the data. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-02 : 05:53:54
|
| You mean you want to ignore things like 23 and 23a in 23 whatever road and 23a whatever road?So ignore the first word if it start's with a number?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
girishkardam
Starting Member
13 Posts |
Posted - 2008-04-02 : 05:57:00
|
| Yes I want to do the same. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-02 : 06:00:22
|
| Select * from Table WHERE Address LIKE '[^0-9]%' |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-02 : 06:03:45
|
| select right(address,len(address) - case when left(address,1) like '[0-9]' then patindex('% %',address) else 0 end)from tblIf you don't have fairly clean addresses then you will probably have a lot ofexceptions to deal with.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
girishkardam
Starting Member
13 Posts |
Posted - 2008-04-02 : 06:07:38
|
Hi,First of all thanks for the reply. It will work fine when address start with numeric values like '1 Khajuri Bazar' but result is not coming properly if address is like 'G-10 Johari Palace' in this i want to ignore G-10.quote:
Originally posted by visakh16
Select * from Table WHERE Address LIKE '[^0-9]%'
|
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-02 : 06:14:03
|
| That's if the first word contains a number?tryselect right(address,len(address) - case when patindex('% %',address) > patindex('%[0-9 ]%',address) then patindex('% %',address) else 0 end)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-04-02 : 06:23:30
|
| If first word always contains a number thenSelect substring(col,charindex(' ',col)+1,len(col)) from your_tableMadhivananFailing to plan is Planning to fail |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
Ignoring Numeric & Alphanumeric values