string manipulation in a vew

[learner45]

Hi,

I have a column named 'volume' having string data in the form:

1234x4563x46586

Is it possible to split it at 'x', then multiply the resulting 3 fields and show as a new column in a view?

Basically 'x' is acting as 'delimiter'.

Thanks!

 

[gmmastros]

You could try....

Select Convert(BigInt, Parsename(Replace(YourColumnName, 'x','.'), 1))
       * Convert(BigInt, ParseName(Replace(YourColumnName, 'x','.'),2))
       * Convert(BigInt, ParseName(Replace(YourColumnName, 'x','.'),3)) As MyNewColumn

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[learner45]

Sorry I overlooked some of the rows.

some rows are in a format (showing volume in cubic meter):

1234x4563x46586 m3

Thanks for your reply!

 

[gmmastros]

If the data is not in a consistent format, I think you should spare yourself huge amounts of pain and completely re-think this process. Start by learning about [google]database normalization[/google]. Your data is NOT normalized because you have a single column storing 3 or 4 pieces of data.

Good luck.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Emtucifor]

It also sounds a bit like you want to build a complex expression parser. That kind of thing is generally not what SQL Server is best for. That is usually the domain of an application program that knows how to parse the string to find tokens and operators and then apply the transformations needed in the correct order to achieve the final result. This is NOT a set-based operation. And, it may be achievable with simple replaces, but only for a very narrow "vocabulary" that cannot be deviated from.

 

[markros]

Some links on the topic that may be helpful

http://en.wikipedia.org/wiki/Reverse_Polish_notation

http://www.maths.abdn.ac.uk/~igc/tch/mx4002/notes/node74.html

http://www-128.ibm.com/developerworks/java/library/j-w3eval/j-w3eval.html

I once was helping another developer with a project and had to write a parser. I didn't, I used evaluate function in VFP and some string translation functions, but I at least looked through these links.

Also Christof Wollenhaupt sent me at that time his parser.