Column troubles

I've got a table which goes something like this:
id usertext1 usertext2 usertext3 label1 label2
label3
123 12 12A12345 BPU
Charge
124 08B14501 8 Charge
BPU
.....
When I want to make a select statement I know which column has "Charge", but
I don't know where "BPU" is. This can be in a different column each time.
How can I make a select statement to find out in which column the "BPU" value
is. Something like: select (something) where id='123'
I want to fit it into a webpage, but I have to get the right select statement
first.
I pick up the "Charge" column from the page and want to set the first two
digits from the accompanying column (e.g. label2/usertext2) in the column
where the "BPU" is.
This is probably not very clear, but it's a bit difficult to explain.
So if you need further information to help me, please ask, because I really
need some help here.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1Hi
Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data
If you wish to select any rows that contains BPU in label1 or label2 use
something like
SELECT id,usertext1,usertext2,usertext3,label1,label2
FROM MyTable
WHERE label1 = 'BPU' OR label2 = 'BPU'
If you want to select them in different orders
SELECT id,usertext1,usertext2,usertext3,label1,label2
FROM MyTable
WHERE label1 = 'BPU'
UNION
SELECT id,usertext1,usertext2,usertext3,label2,label1
FROM MyTable
WHERE label2 = 'BPU'
Therefore the column containing 'BPU' will always be the 5th column in the
result set.
John
"Rune Thandy via SQLMonster.com" <u17273@.uwe> wrote in message
news:65efb7eb91074@.uwe...
> I've got a table which goes something like this:
>
> id usertext1 usertext2 usertext3 label1
> label2
> label3
> 123 12 12A12345 BPU
> Charge
> 124 08B14501 8 Charge
> BPU
> .....
>
> When I want to make a select statement I know which column has "Charge",
> but
> I don't know where "BPU" is. This can be in a different column each time.
> How can I make a select statement to find out in which column the "BPU"
> value
> is. Something like: select (something) where id='123'
> I want to fit it into a webpage, but I have to get the right select
> statement
> first.
> I pick up the "Charge" column from the page and want to set the first two
> digits from the accompanying column (e.g. label2/usertext2) in the column
> where the "BPU" is.
> This is probably not very clear, but it's a bit difficult to explain.
> So if you need further information to help me, please ask, because I
> really
> need some help here.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1
>

Column troubles

I've got a table which goes something like this:
id usertext1 usertext2 usertext3 label1 label2
label3
123 12 12A12345 BPU
Charge
124 08B14501 8 Charge
BPU
.....
When I want to make a select statement I know which column has "Charge", but
I don't know where "BPU" is. This can be in a different column each time.
How can I make a select statement to find out in which column the "BPU" valu
e
is. Something like: select (something) where id='123'
I want to fit it into a webpage, but I have to get the right select statemen
t
first.
I pick up the "Charge" column from the page and want to set the first two
digits from the accompanying column (e.g. label2/usertext2) in the column
where the "BPU" is.
This is probably not very clear, but it's a bit difficult to explain.
So if you need further information to help me, please ask, because I really
need some help here.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200609/1Hi
Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data
If you wish to select any rows that contains BPU in label1 or label2 use
something like
SELECT id,usertext1,usertext2,usertext3,label1,
label2
FROM MyTable
WHERE label1 = 'BPU' OR label2 = 'BPU'
If you want to select them in different orders
SELECT id,usertext1,usertext2,usertext3,label1,
label2
FROM MyTable
WHERE label1 = 'BPU'
UNION
SELECT id,usertext1,usertext2,usertext3,label2,
label1
FROM MyTable
WHERE label2 = 'BPU'
Therefore the column containing 'BPU' will always be the 5th column in the
result set.
John
"Rune Thandy via droptable.com" <u17273@.uwe> wrote in message
news:65efb7eb91074@.uwe...
> I've got a table which goes something like this:
>
> id usertext1 usertext2 usertext3 label1
> label2
> label3
> 123 12 12A12345 BPU
> Charge
> 124 08B14501 8 Charge
> BPU
> .....
>
> When I want to make a select statement I know which column has "Charge",
> but
> I don't know where "BPU" is. This can be in a different column each time.
> How can I make a select statement to find out in which column the "BPU"
> value
> is. Something like: select (something) where id='123'
> I want to fit it into a webpage, but I have to get the right select
> statement
> first.
> I pick up the "Charge" column from the page and want to set the first two
> digits from the accompanying column (e.g. label2/usertext2) in the column
> where the "BPU" is.
> This is probably not very clear, but it's a bit difficult to explain.
> So if you need further information to help me, please ask, because I
> really
> need some help here.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200609/1
>

Collation troubles with SQLServer Express 2005

Greetings,
I installed SQL Server Express 2005 in my system, and created a simple database to store mailng addresses. The database has an email field and a name field. The data to populate the database came from an Excel file saved as a Unicode Text file containing the data as:
xxx@.somemail.com; To?o Peres yyy@.someothermail.com; Iván Cárdenas Note that this data has names with accented characters.
I then tried to import the data into the SQLServer Express with the following command:
bulk insert lista
from 'c:\temp\todas.csv'
with (
DATAFILETYPE ='widechar',
FIELDTERMINATOR =';'
)
I got the following error message:
Bulk load: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.
And the data was imported as:
xxx@.somemail.com To±o Peres yyy@.someothermail.com Ivfn C?rdenas
I tried using the DATAFILETYPE ='native' and DATAFILETYPE ='widenative' options but these caused the following error:
Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

I want to avoid re-installing the server and the Express 2005 edition does not come with the tools needed to rebuild the master database anyway (see this link).
When I type the data with the Server Management Studio Express program, I can insert the accented characters correctly.
Does anyone out there know how to deal with this problem? How do I tell SQL Server Express 2005 to insert the text from the text file and keep the accented characters as they were initially?
I will welcome any help.
Ivan.

Hi Ivan,

Are you sure you saved the spreadsheet as Unicode Text? The .csv filetype suggests you didn't - unless you manually changed it.

I tried using the data above in Excel, saving as Unicode Text and then bulk inserting and it worked fine.

Thanks