Using SUBSTR with Variables - 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 2000 Forums

SQL Server Development (2000)

Using SUBSTR with Variables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-06-22 : 09:49:33

Alan writes "I am trying to parse a text string (field name RAWDATA) using the T-SQL SUBSTRING function. The starting location and ending location are two other fields (LOC1 and LOC2), so the intermediate piece of the RAWDATA string I'm trying to extract and store in field VAL1 begins at the value in field LOC1 and ends one character before the value in field LOC2.

I thought I could do it like this:

update MYTABLE set VAL1 = substring(RAWDATA, LOC1, LOC2-LOC1+1)

However, SUBSTRING doesn't like field references as parameters. It also won't accept declared variables. Looks to me like it only works with hard-coded integer values.

Can you help? I'm on the verge of dumping this data back out to comma-delimited ASCII and writing a VB6 executable to finish the parsing and then sucking it back into SQL Server, which is way too many steps and a pain in the butt.

Any answer you can provide is much appreciated!

Best regards,

Alan Brobst
alan.brobst@trans.ge.com"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-06-22 : 09:52:28

quote:
However, SUBSTRING doesn't like field references as parameters. It also won't accept declared variables.

Works fine for me:

create table #a(name varchar(20), start int, stop int)
insert #a select 'hello there', 4, 8
select substring(name, start, stop-start) from #a
declare @a int, @b int
select @a=1, @b=5
select substring(name, @a, @b) from #a
update #a set name=substring(name, start, stop-@a)
drop table #a

If you have nulls or zeros in the LOC1 or LOC2 columns then you'll get an error or the wrong substring.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-22 : 11:15:40

Are LOC1 and LOC2 numeric fields or string fields?

Peter Larsson
Helsingborg, Sweden

rahul.vairagi068
Starting Member

5 Posts

Posted - 2011-04-11 : 11:36:59

hi,
i have same problem but somehow different, as i need to split a field based on other fields(i.e. start_lenght, end_length) of a table of different database.

e.g.

Database A
table Apple
------------
item item_desc
1200 1200D1HDT765VH5602MGXW
1300 1300D2JHLSK29866JHS98Q

Database B
table Bat
-------------
modelcode type start_length stop_length
1200 model 1 4
1200 material 5 1
1200 conduit 6 3
1300 model 1 4
1300 material 7 1
1300 conduit 8 2

can anyone answer it, i need it urgently

rahul vairagi

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-11 : 11:41:38

SELECT A.Item, B.type, SUBSTRING(A.Item_desc, B.start_length, b.stop_length)
FROM DatabaseA.dbo.Apple A
INNER JOIN DatabaseB.dbo.Bat B ON A.item=B.modelcode

rahul.vairagi068
Starting Member

5 Posts

Posted - 2011-04-12 : 06:21:31

quote:
Originally posted by robvolk

SELECT A.Item, B.type, SUBSTRING(A.Item_desc, B.start_length, b.stop_length)
FROM DatabaseA.dbo.Apple A
INNER JOIN DatabaseB.dbo.Bat B ON A.item=B.modelcode

hi dear,

i think it is known to u that we cant use reference or any variable with SubString function. Kindly help me with good answers....!

rahul vairagi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-12 : 07:19:39

Read Books Online. Of course you can use variables in SUBSTRING function.
Are you sure you are using MICROSOFT SQL SERVER?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-12 : 07:23:55

[code]DECLARE @Apple TABLE
(
ITEM INT,
ITEMDESC VARCHAR(20)
)

INSERT @Apple
SELECT 1200, 'D1HDT765VH5602MGXW' UNION ALL
SELECT 1300, 'D2JHLSK29866JHS98Q'

DECLARE @Bat TABLE
(
modelcode INT,
type varchar(20),
start_length int,
stop_length int
)
INSERT @Bat
SELECT 1200, 'model', 1, 4 UNION ALL
SELECT 1200, 'material', 5, 1 UNION ALL
SELECT 1200, 'conduit', 6, 3 UNION ALL
SELECT 1300, 'model', 1, 4 UNION ALL
SELECT 1300, 'material', 7, 1 UNION ALL
SELECT 1300, 'conduit', 8, 2

SELECT a.ITEM,
b.type,
SUBSTRING(a.ITEMDESC, b.start_length, b.stop_length) AS Data
FROM @Apple AS a
INNER JOIN @Bat AS b ON b.modelcode = a.ITEM[/code]

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-12 : 08:06:43

quote:
Kindly help me with good answers....!

Kindly help by reading Books Online, as Peter suggested, or...try this out...THE EXAMPLE POSTED JUST ABOVE YOUR POST (4+ years earlier, hasn't gone stale in the meantime)

rahul.vairagi068
Starting Member

5 Posts

Posted - 2011-04-12 : 11:48:43

hi peso,

thanks for ur reply, it is working but i need some more help from you and others..

Can the result be like this

model material conduit
1200 D1HD T 765
1300 D2JH K 29

rahul vairagi

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-12 : 11:55:16

rahul.vairagi068
Starting Member

5 Posts

Posted - 2011-04-14 : 11:57:41

a lots of thanks to you...this query has worked quite fine for me

rahul vairagi

rahul.vairagi068
Starting Member

5 Posts

Posted - 2011-04-14 : 12:10:25

hi,

one more question for you...related with previos one

sometimes 'type' i.e.(model, material,conduit,etc) keep changing,
its not static feild ,i need to set them as dynamic ,
Can we put them in some variable so that whenever the field 'type' will change, it will be reflected in my query

Actually i am using this query in excel reporting, so i dont want to change it every time.

how to acheive this ...

rahul vairagi

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-14 : 13:27:54

If you're ultimately going to present all this in Excel you'd be better off querying the raw data into Excel and do the formulas and pivoting there instead.

You can do dynamic pivoting columns like this:

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Although it doesn't sound like that's what you need.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources