Showing posts with label string. Show all posts
Showing posts with label string. Show all posts

Friday, March 23, 2012

Reformatting numeric value to zero-filled string

I need to take a numeric field that I need converted to a zero-filled text
value, for example:
123456 convert to 000123456
I've looked at the CONVERT and CASE and haven't come up with a combination
that will perform this function.
Thanks!
Dougtry this as an example:
SELECT ISNULL(RIGHT('000' + CONVERT(VARCHAR, '123456'), 10), '')
yosh
"Leighton.d" <Leightond@.discussions.microsoft.com> wrote in message
news:464BC261-BE2F-4799-B179-B13C0B1DFAB6@.microsoft.com...
>I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||select replace(str( 10,10),' ','0')
"Leighton.d" <Leightond@.discussions.microsoft.com> wrote in message
news:464BC261-BE2F-4799-B179-B13C0B1DFAB6@.microsoft.com...
>I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||Try,
declare @.num int
set @.num = 123456
select right(replicate('0', 9) + ltrim(@.num), 9)
go
"Leighton.d" wrote:

> I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||Wouldn't it be nice if the Access FORMAT$ function were available.
Thanks for all of the input...
Doug|||As a good practice, try the formatting in the client side.
AMB
"Leighton.d" wrote:

> Wouldn't it be nice if the Access FORMAT$ function were available.
> Thanks for all of the input...
> Doug

Wednesday, March 21, 2012

Referring to Sql connection string in web.config from code behind

Every time I move my project from my computer to the testing server I have to change the connection string references in the aspx side and in my code behind.

For the code behind I declared the SqlConnection string at the top of the code-behind page for both connection strings and comment out the one not in use. Obviously I then comment out the string not in use in the WebConfig as well.

Being superlatively lazy I always look for the easiest and quickest way to do most anything - connection strings included. Having to comment out strings twice brought rise to the question of whether I can refer to the connection string in the web.config file from the code-behind page. I'm sure it can be done, and I did a good amount of hunting around, but I couldn't find any examples of code that would do that.

Currently, as I said above, I have two connection strings declared at the top of my code-behind. Here's an example of one:

Private sqlConnAs New SqlConnection("Data Source=DATABASESERVER;Initial Catalog=DATABASE;User ID=USER;Password=PASSWORD")

Then, I just use sqlConn as usual in my binding without having to "dim" it in every sub:

sdaPersonnel =New SqlDataAdapter(strSqlPersonnel, sqlConn)

Then there's the SqlConnections set up by the wizard on the aspx side:

<asp:SqlDataSource ID="sqlDataSourcePayrollCompany" Runat="Server" ConnectionString="<%$ ConnectionStrings:DATABASECONNECTIONSTRING%>" ...>

And for the connection in the web.config:

<add name="DATABASECONNECTIONSTRING" connectionString="Data Source=DATABASESERVER;Initial Catalog=DATABASE;User ID=USER;Password=PASSWORD" providerName="System.Data.SqlClient" />

So, what would be the code in the code-behind page to refer to the connection string in the web.config file?

Thanks!

To refer to the connection string via code: ConfigurationManager.ConnectionStrings["<ConnectionStringName>"]|||

Hey, Stiletto, Thanks! That did the trick (or, at least, it got me on the right track). I wasn't exaclty sure how to actually add that to my connection string code, so I had to do some hunting and trying different syntax, but ended up with this working:

Private sqlConnAs New SqlConnection(ConfigurationManager.ConnectionStrings("DATABASECONNECTIONSTRING").ConnectionString)
Thanks again!

Referential Integrity with empty string and ZERO

Hi,
How could I define referential integrity using FK constraint which allow me to have empty string/ZERO number instead of NULL value ?

Thank you

If you place a foreign key constraint on a column, zero is only a valid value if there is a zero in foreign key table. The same applies to empty strings.

If the foreign key value of a particular row is unknown (or it doesnt have one), that's where the use of null comes in. Of course a NOT NULL constraint used in conjunction with the FK constraint can prevent the use of nulls (forcing a valid FK value).

To the best of my knowledge, it's not possible to change this behavior.

Referential Integrity with empty string and ZERO

Hi,
How could I define referential integrity using FK constraint which allow me to have empty string/ZERO number instead of NULL value ?

Thank you

If you place a foreign key constraint on a column, zero is only a valid value if there is a zero in foreign key table. The same applies to empty strings.

If the foreign key value of a particular row is unknown (or it doesnt have one), that's where the use of null comes in. Of course a NOT NULL constraint used in conjunction with the FK constraint can prevent the use of nulls (forcing a valid FK value).

To the best of my knowledge, it's not possible to change this behavior.

Monday, February 20, 2012

Reduce string to binary comparison

Is it possible to create a binary comparison from a string? For numbers, you can use this syntax:

Value * (1-ABS(SIGN([Column Name] - [Desired Value))) which will evaluate to one or zero.

For strings, I thought it would be easier. I tried:

Value * ([Column Name]=[Desired Value])

But that does not work. I get an "incorrect syntax near = error. I am looking for a solution that does not rely on the case statement.

Thanks,

AndrewComparison operators return a Boolean data type - TRUE, FALSE, UNKNOWN. As such, you cannot perform arithmetic on them. You are stuck with a CASE statement, or you could use a UDF to do the comparison and return a 1 or 0.

Terri|||Thanks for your response.

Andrew