Extract portion of a character string -- sql server programming

Tina
6/27/2004 7:53:14 PM

Hi Steve,

Thank you very much for your help! It'll definately work.

I have a further question:

If there're 2 columns with hundreds of thousands rows of
these kind of strings(JG4NA126YT) in 2 differenct tables.
And there's another column with the number of positions
in string(4568, 578, etc. Many different numbers)

And I want to join the rows by those positions of
characters in string depending on the numbers in this
third column.

Is there any other suggestion to handle large number of
rows, or have to extract those characters first and match
the rows?

I hope I'm not confusing you.

Thank you again.

Tina

[quoted text, click to view]

Steve Kass
6/27/2004 9:31:57 PM

Tina,

SUBSTRING can return a single character, so you can do what you want as

SUBSTRING(x,4,1) + SUBSTRING(s,5,1) + SUBSTRING(s,6,1) + SUBSTRING(s,8,1)
and
SUBSTRING(s,5,1) + SUBSTRING(s,7,1) + SUBSTRING(s,8,1)

If there is something within each value to indicate what characters
should be extracted, and you want one expression to handle the different
requirements, you'll have to explain what is special about the
particular sequences of characters you want to extract.

Steve Kass
Drew University

[quoted text, click to view]

Steve Kass
6/28/2004 2:57:52 AM

Tina,

It may not be very fast, but you should be able to do it this way:

create table T (
s varchar(20),
p varchar(9)
)
insert into T values ('abcdefghij', '134')
insert into T values ('abcdefghij', '786')
go

select
p, s,
substring(s,p/1000000000%10,1) +
substring(s,p/100000000%10,1) +
substring(s,p/10000000%10,1) +
substring(s,p/1000000%10,1) +
substring(s,p/100000%10,1) +
substring(s,p/10000%10,1) +
substring(s,p/1000%10,1) +
substring(s,p/100%10,1) +
substring(s,p/10%10,1) +
substring(s,p%10,1)
from T
go

drop table T

SK

[quoted text, click to view]

Uri Dimant
6/28/2004 10:55:00 AM

Tina
This one is less elegant than Steve
I used numbers table to separate P column

SELECT IDENTITY(INT) "num" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2

create table T (
s varchar(20),
p varchar(9)
)
insert into T values ('abcdefghij', '134')
insert into T values ('abcdefghij', '786')
go

select p,s,substring(s,cast(pos as int),1) from
(
select p,substring(p,num,1)as pos ,s, (select count(*) from t t1
where t.p>=t1.p) as c
from t left join numbers n
on substring(t.p,1,num)>=n.num and substring(p,num,1)<>''
) as d

PS. I know it is not exactly what you wanted but you can make it as a view
and then contaconate it.

[quoted text, click to view]

Tina
6/28/2004 6:14:27 PM

Thank you both. I really appreciate. I'll try both ways.

Tina

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Extract portion of a character string