SUBSTRING / TRIM help? 1

[wickyd]

Good day folx

I am new to SQL and I need some help with the following:


What is the SQL that will transform

abc(red) 10
abc(black) 20
abc(blue) 15
a(red) 2
a(green) 4
a(orange) 5


into ...


abc 45
a 11

Thank you.

Kind regards
wickyd

 

[JamesLean]

SELECT LEFT(col1, CHARINDEX('(', col1) - 1), col2
FROM mytable

--James

 

[JamesLean]

BTW, this assumes you have the parentheses in every value in the field. If you don't then do a check with:

SELECT CASE WHEN CHARINDEX('(', col1) <> 0 THEN LEFT(col1, CHARINDEX('(', col1) - 1) ELSE col1 END
...

--James

 

[wickyd]

My apologies, I am not using MS SQL server, I am using DBISAM, which doesn't support what you have suggested.

Any pure SQL way to do this? The only LEFT I can use is LEFT OUTER JOIN

Thank you.

 

[AtomicWedgie]

Maybe in DBISAM the function LEFT is presented like LTRIM or TRIM or so?

 

[JamesLean]

You could try this:

SELECT SUBSTRING(col1, 1, CHARINDEX('(', col1) - 1)

I can only really advise on SQL Server as this is what I know (and after all this is a SQL Server forum!)

--James

 

[wickyd]

I know this is not the correct place, but I know how helpful this forum is

DBISAM does not support CHARINDEX, but it does support TRIM.

Argh, we are getting closer

 

[wickyd]

If you could provide me with a multiple replace, I could then simply replace each colour string with a null string .. what do you think?

select code, replace("(red)" with "&quotstripedcolour from stock

the replace simply needs to be expanded to include 5 to 10 colours.

 

[JamesLean]

CHARINDEX and TRIM (in SQL Server at least) do two different things.

CHARINDEX finds an occurance of one string in another. In my query, I use it to find where the first '(' was and just take the first part of the value up to that point (using the LEFT or SUBSTRING function).

LTRIM/RTRIM get rid of leading/trailing spaces from a value. In SQL Server, there is no TRIM function.

It looks like you need to find a function which does the same as the CHARINDEX function. Let us know if you come up with one.

--James

 

[JamesLean]

You can nest REPLACEs like this:

SELECT REPLACE(REPLACE(col1, '(red)', ''), '(blue)', '')

But using this seems a horribly ugly solution to your problem, makes maintenance hard (what happens when another colour comes up?) and - most of all - will perform badly.

--James

 

[wickyd]

Thank you so much for the pointers

Here goes:

SELECT CODE, SUBSTRING(CODE FROM 1 FOR POS("(" IN CODE) - 1)STRIPPEDCODE FROM STOCK
WHERE POS("(" IN CODE) > 0

POS returns the position of one string within another string. I have to use the WHERE clause as DBISAM does not support the CASE element.

The accounting software I use, uses DBISAM. Their support team told me that what I was asking them for was impossible.

Thank you for proving them wrong.

 

[pascalsql]

Try this ???

SUBSTRING(column_reference or string constant FROM start_index [FOR length])
POS(string constant IN column_reference or string constant)
REPLACE(string constant WITH new string constant IN
column_reference or string constant)

Tip: search on the web 'dbisam susbtring' to find a manual, documentation or examples. I found the above here

http://www.elevatesoft.com/manual_Functions.htm