How to pull exact string - 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

SQL Server 2005 Forums

Transact-SQL (2005)

How to pull exact string

Author

Topic

treeskin
Starting Member

22 Posts

Posted - 2009-02-06 : 03:25:16

Hello Experts,

I need help!

I need to pull a list of records where the ID number is within the exact length. Below is a sample data and result.

ID Item
12-123 Pencil
12-125 Pen
310-145 A4 File
429-209R Glue

The script needs to pull out below Result:
ID Item
12-123 Pencil
12-125 Pen

The ID is always have an hyphen and I would like to pull all records where ID format like xx-xxx.

Your help is very much appreciated.

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-06 : 03:29:02

SELECT * FROM Table1 WHERE ID LIKE '[0-9][0-9]-[0-9][0-9][0-9]'
SELECT * FROM Table1 WHERE ID LIKE '[0-9a-z][0-9a-z]-[0-9a-z][0-9a-z][0-9a-z]'

E 12°55'05.63"
N 56°04'39.26"

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-02-06 : 03:29:35

use Len() string function to identify the exact length..

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-06 : 03:30:13

select * from urtblae where charindex('-',Columnname) = 3

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-06 : 03:31:47

declare @t table (ID varchar(32), Item varchar(32))
insert into @t select
'12-123', 'Pencil' union all select
'12-125',' Pen' union all select
'310-145', 'A4 File' union all select
'429-209R', 'Glue'

select * from @t where left(id, charindex('-',id,1)-1) = 12

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-02-06 : 03:32:25

I hope Peso.. solution is goood.. Go with Peso..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-06 : 03:33:13

quote:
Originally posted by Nageswar9

select * from urtblae where charindex('-',Columnname) = 3

Why did you remove your previous answer?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-06 : 03:36:24

quote:
Originally posted by bklr

declare @t table (ID varchar(32), Item varchar(32))
insert into @t select
'12-123', 'Pencil' union all select
'12-125',' Pen' union all select
'310-145', 'A4 File' union all select
'429-209R', 'Glue'

select * from @t where left(id, charindex('-',id,1)-1) = 12

Add this sample data and try again

'22-125',' Bklr' union all select

And when you are at it, add this sample data too!

'a2-125',' Bklr again' union all select

E 12°55'05.63"
N 56°04'39.26"

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-06 : 03:37:59

declare @temp table(ID vARCHAR(32), Item varchar(32))
insert into @temp
select '12-123', 'Pencil' union all
select '12-125' ,'Pen' union all
select '310-145' ,'A4' union all
select '02-209R' ,'Glue'
SELECT * FROM @temp WHERE ID LIKE '__-___'

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-06 : 03:42:50

that fine peso
i had changed the query once check
declare @t table (ID varchar(32), Item varchar(32))
insert into @t select
'12-123', 'Pencil' union all select
'12-125',' Pen' union all select
'310-145', 'A4 File' union all select
'22-125',' Bklr' union all select
'a2-125',' Bklr again' union all select
'429-209R', 'Glue'

select * from @t where len(left(id, charindex('-',id,1)-1)) = 2 and len(substring(id,charindex('-',id,1)+1,len(id)))= 3

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-06 : 03:49:04

Add this sample data

'9 -125',' Bklr third' union all select

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-06 : 03:55:42

k fine

select * from @t where datalength(left(id,charindex('-',id,1)-1)) = 2 and datalength(substring(id,charindex('-',id,1)+1,len(id)))= 3

treeskin
Starting Member

22 Posts

Posted - 2009-02-06 : 04:00:10

Hello Experts, thanks for the response. I tried it and it all works fine.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-06 : 04:00:14

I am just pulling your legs!

With your approach, you can never use an existing index, because you have made calculations over the columns used for filtering.
And if OP really wants "numeric-numeric-dash-numeric-numeric-numeric" neither of your suggestions will work.

E 12°55'05.63"
N 56°04'39.26"

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-06 : 04:04:00

Welcome

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-06 : 04:21:34

"numeric-numeric-dash-numeric-numeric-numeric"

declare @t table (ID varchar(32), Item varchar(32))
insert into @t select
'12-123', 'Pencil' union all select
'12-125',' Pen' union all select
'310-145', 'A4 File' union all select
'22-125',' Bklr' union all select
'a2-125',' Bklr again' union all select
'429-209R', 'Glue'

select * from @t where isnumeric(left(id, charindex('-',id,1)-1))= 1 and len(left(id, charindex('-',id,1)-1) )= 2and isnumeric(substring(id,charindex('-',id,1)+1,len(id))) = 1 and len(substring(id,charindex('-',id,1)+1,len(id))) = 3

Jai Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-06 : 04:31:34

Which is easier to write, read and maintain than this?

SELECT * FROM Table1 WHERE ID LIKE '[0-9][0-9]-[0-9][0-9][0-9]'

And try again with this sample data

'$0-£12',' Peso' union all select

E 12°55'05.63"
N 56°04'39.26"

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-06 : 04:33:43

quote:
Originally posted by Peso

Which is easier to write, read and maintain than this?

SELECT * FROM Table1 WHERE ID LIKE '[0-9][0-9]-[0-9][0-9][0-9]'

And try again with this sample data

'$0-£12',' Peso' union all select

E 12°55'05.63"
N 56°04'39.26"

Its Ok urs effective

Jai Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-06 : 04:37:41

The speed and effectiveness is a bonus.
The real gain is that it is correct for "numeric-numeric-dash-numeric-numeric-numeric".

SQL Server built-in ISNUMERIC is not that reliable.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-06 : 09:45:18

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources