Finding position in a string

Question

Post reply

Finding position in a string

kglassman

Ten Centuries

Points: 1167
More actions
October 14, 2005 at 7:19 am

#95444

I'm hoping someone out there can figure out how to do this. I have a column that is varchar (300) that contains a string that represents special order information for product. Here are some examples of the data:

F234-000 BAL:2199 42 CUSHIONDLM FINISH_STD:ES TP:1937 86 FRINGE_STD:NONE
9258-000 BAL:10010 32 CUSHIONDLM ARM_SHIELD:N FRINGE_STD:NONE SIDE:L TP:3943 12
F49-000 BAL:4743 64 FINISH_STD:ES
What I need to do is search through the string, find the placement of the "BAL" and then do a substring to pull back the information from the starting position of BAL. In the examples above, the output would be:

BAL:2199 42
BAL:10010 32
BAL:4743 64

I would also like to parse out cushion, finish_std, etc. Any help would be greatly appreciated.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

rsharma SSCarpal Tunnel Points: 4865 More actions · Answer 1

Use the charindex() function to find the starting position of the string, example:

declare @string varchar(100)

set @string = 'F49-000 BAL:4743 64 FINISH_STD:ES'

select charindex ('bal', @string)

--output

9

And use this information and put a substring() function around the charindex code and use the output from charindex as the starting point for the substring.

kglassman Ten Centuries Points: 1167 More actions · Answer 2

Okay, maybe I'm being a bit dense, but I don't see how charindex will work. I need to search all of the rows where the starting position may change based on the data in that row.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

You can use charindex on a column as well to fin the position of a string. Then you can use that information to manipulate the data as needed.

kglassman Ten Centuries Points: 1167 More actions · Answer 4

So basically, (if I understand correctly...have never used charindex before) if the column is named specorderinfo then:

select substring(charindex('BAL',specorderinfo)1,12)

Or something close to that?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Not quite... check out the books online for the correct syntax.

kglassman Ten Centuries Points: 1167 More actions · Answer 6

I have been able to get the following but cannot figure out how to get the substring to work:

select orderid, productid, charindex('BAL',VendorModelNbr)as 'StartPos', vendormodelnbr

from orderitem

where writtendate = '01-Sep-05'

and vendorid = '50R'

Results:

orderid productid startpos

2912498273 50R9256LSE-SO 0 9256-000

2922498653 50RF710S-SO 11 F710S-000 BAL:1226 88 TP:1226 88 CW:1226 88 FRINGE_STD:NONE

2932498997 50RC750-SO 10 C750-000 BAL:2898 04 CUSHIONDLM ARM_SHIELD:N TP:2898 04 FINISH_STD:ES FRINGE_STD:NONE

Even reading through bol I am unsure how I should phrase the syntax to get the substring starting at BAL and ending after 12 digits.

sushila SSC-Dedicated Points: 35293 More actions · Answer 7

using rsharma's solution...here's how you would use the substring...

declare @string varchar(100)
set @string = 'F49-000 BAL:4743 64 FINISH_STD:ES'
select charindex ('bal', @string)

select substring(@string, charindex('bal', @string), 11)

**ASCII stupid question, get a stupid ANSI !!!**

David Burrows SSC Guru Points: 65111 More actions · Answer 8

and.......

if the number of digits following BAL is variable then

select substring(@string, charindex('bal', @string), patindex('%[a-z]%',substring(@string, charindex('bal', @string)+4, len(@string)))+3)

as for the rest of the parsing we will need for accurate definition of the data and how that data is separated

Far away is close at hand in the images of elsewhere.
Anon.

kglassman Ten Centuries Points: 1167 More actions · Answer 9

Okay the girl is brain dead (it is Friday!)........!!!!

I need to know how in the syntax to use the column name to find "bal" in not just to find a set string. Ie: if I use:

declare @string varchar(100)

set @string = 'F49-000 BAL:4743 64 FINISH_STD:ES'

select charindex ('bal', @string)

select substring(@string, charindex('bal', @string), 11)

from orderitem

where writtendate = '01-sep-05'

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

select substring(ColumnName, charindex('bal', ColumnName), 11)

from orderitem

where writtendate = '01-sep-05'

kglassman Ten Centuries Points: 1167 More actions · Answer 11

You are wonderful! Thank you so much, and have a great weekend!

sushila SSC-Dedicated Points: 35293 More actions · Answer 12

ha David - another one for remi! Not that anyone's keeping tabs!!!

btw:which of the 2 avatars you've posted so far is "more you" ?!?! hard to tell the difference!

**ASCII stupid question, get a stupid ANSI !!!**

David Burrows SSC Guru Points: 65111 More actions · Answer 13

ha David - another one for remi! Not that anyone's keeping tabs!!!

Yeah as always Always comes good at the end

btw:which of the 2 avatars you've posted so far is "more you" ?!?! hard to tell the difference!

Definately this one, just like me

Far away is close at hand in the images of elsewhere.
Anon.

sushila SSC-Dedicated Points: 35293 More actions · Answer 14

"Always comes good at the end"...is that the voice of a sore loser ?!?! If you've noticed - Remi stepped in with a very weary tread - none of the bounce & vigour of "ye olde days"...

as for your new persona - "just like me" - is he "laid back" or "jumping for joy"...can't tell from this distance..

**ASCII stupid question, get a stupid ANSI !!!**