Query help - SQL Server Forums

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

All Forums

General SQL Server Forums

New to SQL Server Programming

Query help

Author

Topic

drpkrupa
Yak Posting Veteran

74 Posts

Posted - 2006-10-03 : 21:30:11

on my select statment i want a use sustring which start with very first chracter as number and last charcter as space.

CA 1123 noel
34434 s madission
house no 39484 waterfall

i need result as
1123
34434
39484

substring first letter with number and last with space.

drpkrupa
Yak Posting Veteran

74 Posts

Posted - 2006-10-03 : 22:11:56

i found this patindex('%[0-9]%',addr1) to get first numeric chacter.
I am thinking to user this
substring(addr1,patindex('%[0-9]%',addr1),?????)

??? - how can i end when i find space or non numric no .
like

kdhfd 22232 kjj
jljk 34343kkk

i need 22232 and 34343
break on space or alpha .

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-03 : 22:21:57

[code]
select *,
n = substring(col, patindex('%[0-9]%', col), charindex(' ', col, patindex('%[0-9]%', col)) - patindex('%[0-9]%', col))
from
(
select 'CA 1123 noel' as col union all
select '34434 s madission' union all
select 'house no 39484 waterfall'
) d

col n
------------------------ ------------------------
CA 1123 noel 1123
34434 s madission 34434
house no 39484 waterfall 39484
[/code]

drpkrupa
Yak Posting Veteran

74 Posts

Posted - 2006-10-04 : 10:53:10

Thanks for you replay.
I am very close to my result.
two more question.
it get failed on this:
'noel st 1223' - because there is no space
i want a make small change. What i want do i want a look first letter after number or space after number. Who ever find first then i want a break at that point.
Like: 'noel 1236 st' -- 1236 - space first
'noel 1236st street' ---- 1236 -- st -letter before space
'noel 1236' --- 1236 - no space or letter after no
'noel 1236st' --- 1236 - first letter st after 1236

Need help please

drpkrupa
Yak Posting Veteran

74 Posts

Posted - 2006-10-04 : 12:47:24

any update.
thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-04 : 19:07:12

"sustring which start with very first chracter as number and last charcter as space"
"it get failed on this ... because there is no space"
Change of requirement ?

try this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33433

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources