select a specific number in a column value

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 a specific number in a column value

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-01-05 : 14:35:28

hi

i have a colomn called customerValue in table called customers in the
following manner

123 7819 1223232323
2323 67819 3434343434
323311 34567 3434343434

The second number i.e 7819,67819 in the coloumn is the customer number

how can i get only that number selected in a query .

Thanks in advance

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-05 : 15:01:52

a few ways to do this. here's one:


select	left (
	right(customervalue, len(customervalue) - charindex(' ', customervalue)),
	charindex(' ', right(customervalue, len(customervalue) - charindex(' ', customervalue)))
)
from	customers

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-05 : 15:17:46

Just another way using the same idea:

Select 
Substring(customervalue,
charindex(' ', customervalue) + 1 ,
 (Len(customervalue) -  charindex(' ', Reverse(customervalue)))- charindex(' ', customervalue) ) from customers

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-01-05 : 15:21:59

Thanks Russell
but when i run the query the following error shows up
Invalid length parameter passed to the RIGHT function.

i guess its due to the fact some of the values is just
single number like
1234 45678 13123
1234
2453545 343434 66666
5454
545

what should i do for this

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-01-05 : 15:24:30

thansk dp but same issue which i reported for russel

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-05 : 15:27:35

Did you try my function?

EDIT: This is working for me?

declare @Customer table
(
customervalue varchar(30)
)
insert into @Customer values('123 7819 1223232323')
insert into @Customer values('2323 67819 3434343434')
insert into @Customer values('323311 34567 3434343434')
insert into @Customer values('546')

Select Substring(customervalue,charindex(' ', customervalue) + 1 , (Len(customervalue) -  charindex(' ', Reverse(customervalue)))- charindex(' ', customervalue) ) from @Customer

Do you have trailing ' ' s? if so RTRIM the field.

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-01-05 : 15:30:54

yes dp978 it throwed this error

Invalid length parameter passed to the SUBSTRING function.

it is a varchar field if this could help you.

thanks

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-05 : 15:32:43

Can you quickly copy and paste my exact code above and see if you get:

7819
67819
34567
546

BTW, does your column have NULL values? if so I do not think it can be passed, you will have to create a case statement around it.

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-01-05 : 15:35:19

just magic dp 978. Thanks for your insightfullness.
thanks to russell too.

one of the best forums...always quick in repsonses.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-05 : 15:37:09

NP, glad to help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-06 : 01:37:46

If you dont need first part if there is no second part

declare @Customer table
(
customervalue varchar(30)
)
insert into @Customer values('123 7819 1223232323')
insert into @Customer values('2323 67819 3434343434')
insert into @Customer values('323311 34567 3434343434')
insert into @Customer values('546')

Select parsename(replace(customervalue,' ','.'),2) from @Customer

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources