Basic SQL help

da_mic_1530 · 2 Feb 2011 at 18:38

Hey all, just need a point in the right direction on a few bits and bobs

in SQL

Using a field value in a query

I have a statement that replaces anything with a blank entry on 1 field and replaces it with EMPTY (This is hardcoded)

I wish to rather than hard code this, to feed the value from a field in test_table

i.e nvl(randomfield = 'I want my value from test_table here')

Secondly

Inserting query records into a table

I would like a bit of help inserting records into a table from a query

e.g

select surname,
a_hard_coded_value (e.g query number 1)
from test_table
into another_test_table (column1, column2)

Finally Identifing White Space
I wish to check the a certain column's field values do not have whitespace

e.g 'value' - is fine
but 'value ' -is flagged

Ciphon · 2 Feb 2011 at 20:19

White Space:

Wrap you value in RTRIM(LTRIM(' value ')) - this will turn your ' value ' into 'value'... and if you haven't guessed already RTRIM is right trim, LTRIM is left trim - there is no generic trim to do both sides.

Inserting query records into a table

You'll want to use something like the below:

INSERT INTO table
(Columns if you want to list them go here)
SELECT column list FROM table

Using a field value in a query

I'm not entirely sure I'm following what you need here.. You have a column which is filled with empty values, but instead of showing the empty value you want to show another value?

Feel free to post your actual SQL if you want me to explain / help further..

da_mic_1530 · 3 Feb 2011 at 19:03

Many thanks, especially on the whitespace, I did a bit of reading today and fixed everything bar the whitespace. So that will prove useful tomorrow.

Again Thanks So Much for a helpful reply

Ciphon · 3 Feb 2011 at 20:37

You're very welcome!

If you want to find the results that have white space on either side of them (so that you can fix them!), use something like the below:

Select Column
From Table
Where Column Like ' %' OR Column Like '% '

da_mic_1530 · 4 Feb 2011 at 20:02

edit - being a numpty!

Thanks again the 2nd whitespace query was the initial request I was after