Select 4 numbers from a string

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

SQL Server 2005 Forums

Transact-SQL (2005)

Select 4 numbers from a string

Author

Topic

inholland
Starting Member

3 Posts

Posted - 2007-05-02 : 06:45:24

Hi!

I have a column with string data.
This data looks like this:

abcde 12 (3456) (bla45)
dlufhiuafh(1234) fsdsdfsdf
(1234)fdgsgdfg(23) fsdfd
sdfdsffs(1234)sdfsd(3)
ffss(1234)ewffs345

I want to get the 4 numbers between ( and ) out of the string.
The data is not nice but there are always 4 numbers in row between ().
So I need some sort of: select 4 numbers between () query.

Can anybody help me with this query?

I use SQL Server 2005.

Kind Regards

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-02 : 06:51:13

[code]-- Prepare sample data
declare @t table
(
a varchar(100)
)

insert into @t
select 'abcde 12 (3456) (bla45)' union all
select 'dlufhiuafh(1234) fsdsdfsdf' union all
select '(1234)fdgsgdfg(23) fsdfd' union all
select 'sdfdsffs(1234)sdfsd(3)' union all
select 'ffss(1234)ewffs345'

-- final query
select substring(a, patindex('%([0-9][0-9][0-9][0-9])%', a)+1, 4) as a
from @t
where patindex('%([0-9][0-9][0-9][0-9])%', a) > 0[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

inholland
Starting Member

3 Posts

Posted - 2007-05-02 : 07:06:50

In one word: Perfect!

Thx a lot! it looks like India has a lot of skilled people after all ;-)

Greetings from The Netherlands!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-02 : 07:26:15

Thanks.

Glad it worked. I hope you also tried to understand how the code works. All the best !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

inholland
Starting Member

3 Posts

Posted - 2007-05-02 : 07:35:28

I understand the code, its just that I did not know the patindex function.
I knew it had to be something with '% %' but I could not find anything right on the net.

But thanks a lot, I will save this query for the future.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-02 : 07:42:25

Just FYI, PATINDEX() is used to search for pattern of characters inside string, not actual characters and it returns starting position of such pattern. In your case, the pattern is (xxxx) where x denotes any digit. It's kinda wild-card search.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources