Add Preceeding Zero

I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?Your datatype for NumVal should be a character type, and you have to use +
instead of &
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"box2003" <box2003@.yahoo.com> wrote in message
news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?|||I had just found the & and + problem, coming in from Access I use the &.
Thank you.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGI49IgYFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Your datatype for NumVal should be a character type, and you have to use +
> instead of &
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "box2003" <box2003@.yahoo.com> wrote in message
> news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
> I have a field in a table, datatype is varchar. The field contains values
> such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
> want to change the field to precede all field values with 0 to yield,
> 01000000, 01000003, 05000001. I have attempted this using the follow but,
> does not work returning error
> UPDATE NumVal_1
> SET NumVal = '0' & NumVal
> WHERE Left(NumVal,1) > 0
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar
> Am I even close to being on the right track?
>
>

Add Preceeding Zero

I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?
Your datatype for NumVal should be a character type, and you have to use +
instead of &
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"box2003" <box2003@.yahoo.com> wrote in message
news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?
|||I had just found the & and + problem, coming in from Access I use the &.
Thank you.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGI49IgYFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Your datatype for NumVal should be a character type, and you have to use +
> instead of &
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "box2003" <box2003@.yahoo.com> wrote in message
> news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
> I have a field in a table, datatype is varchar. The field contains values
> such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
> want to change the field to precede all field values with 0 to yield,
> 01000000, 01000003, 05000001. I have attempted this using the follow but,
> does not work returning error
> UPDATE NumVal_1
> SET NumVal = '0' & NumVal
> WHERE Left(NumVal,1) > 0
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar
> Am I even close to being on the right track?
>
>

Add Preceeding Zero

I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?Your datatype for NumVal should be a character type, and you have to use +
instead of &
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"box2003" <box2003@.yahoo.com> wrote in message
news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?|||I had just found the & and + problem, coming in from Access I use the &.
Thank you.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGI49IgYFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Your datatype for NumVal should be a character type, and you have to use +
> instead of &
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "box2003" <box2003@.yahoo.com> wrote in message
> news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
> I have a field in a table, datatype is varchar. The field contains values
> such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
> want to change the field to precede all field values with 0 to yield,
> 01000000, 01000003, 05000001. I have attempted this using the follow but,
> does not work returning error
> UPDATE NumVal_1
> SET NumVal = '0' & NumVal
> WHERE Left(NumVal,1) > 0
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar
> Am I even close to being on the right track?
>
>

Add apostrophe to column in result set

I have a brain teaser for you all.

The end result: I need one of the columns in my result set (col2) to have a preceeding apostrophe (i.e., '001234 ).

The problem: I am building my query as a string and then using the EXEC() function to execute it. For example:
SET @.strSQL = 'SELECT col1, col2 FROM mytable'
EXEC(@.strSQL)

Just to tame any answers that might not be directed to the specific question, the query Must be built as a string and then executed.

I have been unable to obtain the solution and I am wondering if it is even possible?

TIADo you mean single quote?

Is this what you need?

declare @.sql as varchar(2000)

set @.sql = 'select ''' + companyname as [''name] from customers'
exec(@.sql)

set @.sql = 'select ''`'' + companyname from customers'
exec(@.sql)

:)|||Easy!

'SELECT (CHAR(39) + table.column) FROM table'

:)|||Select [''hi] From Table

to clarify, ' is a delimiter in sql, so you must use two of them to escape it.

Select [''e1234] From YourTable Where [''e1234] = 'SomeValue';

That's how to do it.

Enjoy