Hi,
I have several INT columns in a table that I need to update.
For example, in column 'aa' I need to add 2 to all of the values in that column.
I'm using Query Analyzer - what update statement should I write?Hmm - no answers - should I be in a different forum for this question?
I guess I could do:
UPDATE table_name SET aa = 16 WHERE aa = 14
UPDATE table_name SET aa = 15 WHERE aa = 13
UPDATE table_name SET aa = 14 WHERE aa = 12
and keep going like this until I have all the values updated.
Note that I've done it from highest number first, otherwise all of the data would get adjusted to the two highest numbers.
I was looking for a more elegant solution If anyone can think of one as I have several columns to update all with slightly different increases.|||UPDATE table_name SET aa = aa + 2
:D|||Of course - thank you - how could I miss it??
Woods for the trees and all...
Showing posts with label int. Show all posts
Showing posts with label int. Show all posts
Sunday, March 25, 2012
Thursday, March 8, 2012
Adding new Guid to destination
Source table has two fields, int and string. Destination has three fields, int, string and Uniqueidentifier (Guid). For each source row added to the destination, I need to add a new Guid. I know how to make a new Guid in VB.NET script, but how do I inject it into the transformation for each row. I see the “foreach loop container” component and I suspect I need to use it, but I am stumped. Any help would be most appreciated
Paul SullivanUse script component in the DataFlow (added between source and destination). When you edit it, mark your guid column as read/write input column and in the script template write something like
Row.MyGuid = ...|||
Hi,
I have the same task as you have.
Did you make the script component for adding Guid to each source row?
Could you please help me to understand it?
If you could share some source code and some settings for it - would be great!
Thanks.
|||Vita wrote:
Hi,
I have the same task as you have.
Did you make the script component for adding Guid to each source row?
Could you please help me to understand it?
If you could share some source code and some settings for it - would be great!
Thanks.
Imports System
.
.
.
.
Row.MyGuid = System.Guid.NewGuid()
Regards
Jamie
|||
I did not think that configuration for it would be so easy.
Thanks a lot.
Adding new Guid to destination
Source table has two fields, int and string. Destination has three fields, int, string and Uniqueidentifier (Guid). For each source row added to the destination, I need to add a new Guid. I know how to make a new Guid in VB.NET script, but how do I inject it into the transformation for each row. I see the “foreach loop container” component and I suspect I need to use it, but I am stumped. Any help would be most appreciated
Paul SullivanUse script component in the DataFlow (added between source and destination). When you edit it, mark your guid column as read/write input column and in the script template write something like
Row.MyGuid = ...|||
Hi,
I have the same task as you have.
Did you make the script component for adding Guid to each source row?
Could you please help me to understand it?
If you could share some source code and some settings for it - would be great!
Thanks.
|||
Hi,
I have the same task as you have.
Did you make the script component for adding Guid to each source row?
Could you please help me to understand it?
If you could share some source code and some settings for it - would be great!
Thanks.
Imports System
.
.
.
.
Row.MyGuid = System.Guid.NewGuid()
Regards
Jamie
|||I did not think that configuration for it would be so easy.
Thanks a lot.
Saturday, February 25, 2012
adding identity column
I'm adding an Identity column to a table like this:
SELECT DISTINCT
T.NAME_LAST,
T.NAME_FIRST,
T.DOB,
RowID = IDENTITY(INT, 1, 1)
INTO #TempResultSet
FROM #TempPopulation T
LEFT JOIN #TempDateOfService B
ON B.KEY_ID = T.KEY_ID
ORDER BY NAME_LAST, NAME_FIRST
The problem I'm having is that the RowID isn't added to the table after it's
Ordered, which is what I want/need. Is there a way to do this, without
having to put the data into another temp table, where it's already ordered?
Thanks, AndreSELECT INTO does not guarantee order.
See this KB article:
http://support.microsoft.com/kb/273586
And yesterday's thread in this group:
"Row numbering unpredictable"
If you know the order by is NAME_LAST, NAME_FIRST then what difference does
it make what the identity value is? Never mind that if someone changes
their last name, suddenly your identity values are all messed up.
"Andre" <no@.spam.com> wrote in message
news:OGoht0oXGHA.3740@.TK2MSFTNGP03.phx.gbl...
> I'm adding an Identity column to a table like this:
> SELECT DISTINCT
> T.NAME_LAST,
> T.NAME_FIRST,
> T.DOB,
> RowID = IDENTITY(INT, 1, 1)
> INTO #TempResultSet
> FROM #TempPopulation T
> LEFT JOIN #TempDateOfService B
> ON B.KEY_ID = T.KEY_ID
> ORDER BY NAME_LAST, NAME_FIRST
> The problem I'm having is that the RowID isn't added to the table after
> it's
> Ordered, which is what I want/need. Is there a way to do this, without
> having to put the data into another temp table, where it's already
> ordered?
> Thanks, Andre
>|||Thanks Aaron. The reason I need the identity column in the order of
name_last, name_first is because I'm using the identity column in a sproc
that handles paging. I get my resultset, in this case it's 2000 records,
and I only want to return 25 records to the front-end. I need my data
ordered so I know which 25 to send in. I've got it working by putting my
ordered data into a temp table, then into another temp where I add the ident
column. It just seemed like an extra step, and an extra temp table.
Perhaps not the most optimal solution but it works.
Andre|||> Thanks Aaron. The reason I need the identity column in the order of
> name_last, name_first is because I'm using the identity column in a sproc
> that handles paging.
THERE ARE BETTER SOLUTIONS THAN THIS!
http://www.aspfaq.com/2120|||I'm basically doing exactly what the article showed, but just having to make
use of the temp table to order my data. My method is precisely what I've
seen in SQL Magazine previously.
Thanks Aaron.
Andre
SELECT DISTINCT
T.NAME_LAST,
T.NAME_FIRST,
T.DOB,
RowID = IDENTITY(INT, 1, 1)
INTO #TempResultSet
FROM #TempPopulation T
LEFT JOIN #TempDateOfService B
ON B.KEY_ID = T.KEY_ID
ORDER BY NAME_LAST, NAME_FIRST
The problem I'm having is that the RowID isn't added to the table after it's
Ordered, which is what I want/need. Is there a way to do this, without
having to put the data into another temp table, where it's already ordered?
Thanks, AndreSELECT INTO does not guarantee order.
See this KB article:
http://support.microsoft.com/kb/273586
And yesterday's thread in this group:
"Row numbering unpredictable"
If you know the order by is NAME_LAST, NAME_FIRST then what difference does
it make what the identity value is? Never mind that if someone changes
their last name, suddenly your identity values are all messed up.
"Andre" <no@.spam.com> wrote in message
news:OGoht0oXGHA.3740@.TK2MSFTNGP03.phx.gbl...
> I'm adding an Identity column to a table like this:
> SELECT DISTINCT
> T.NAME_LAST,
> T.NAME_FIRST,
> T.DOB,
> RowID = IDENTITY(INT, 1, 1)
> INTO #TempResultSet
> FROM #TempPopulation T
> LEFT JOIN #TempDateOfService B
> ON B.KEY_ID = T.KEY_ID
> ORDER BY NAME_LAST, NAME_FIRST
> The problem I'm having is that the RowID isn't added to the table after
> it's
> Ordered, which is what I want/need. Is there a way to do this, without
> having to put the data into another temp table, where it's already
> ordered?
> Thanks, Andre
>|||Thanks Aaron. The reason I need the identity column in the order of
name_last, name_first is because I'm using the identity column in a sproc
that handles paging. I get my resultset, in this case it's 2000 records,
and I only want to return 25 records to the front-end. I need my data
ordered so I know which 25 to send in. I've got it working by putting my
ordered data into a temp table, then into another temp where I add the ident
column. It just seemed like an extra step, and an extra temp table.
Perhaps not the most optimal solution but it works.
Andre|||> Thanks Aaron. The reason I need the identity column in the order of
> name_last, name_first is because I'm using the identity column in a sproc
> that handles paging.
THERE ARE BETTER SOLUTIONS THAN THIS!
http://www.aspfaq.com/2120|||I'm basically doing exactly what the article showed, but just having to make
use of the temp table to order my data. My method is precisely what I've
seen in SQL Magazine previously.
Thanks Aaron.
Andre
Thursday, February 16, 2012
Adding data to database with other application not SQL
Hi,
I have a database that it have and int type with identity (1,1).when I add
data from sql everything is ok but when I want to add data from fro example
Delphi it says that field ID must have a value and auto numbering doesn't
work.
Please tell me how can I solve my problem.
Regards
Ali"Ali.M" <AliM@.discussions.microsoft.com> wrote in message
news:4D01366E-E296-4C7E-84FF-58EA93329088@.microsoft.com...
> Hi,
> I have a database that it have and int type with identity (1,1).when I add
> data from sql everything is ok but when I want to add data from fro
example
> Delphi it says that field ID must have a value and auto numbering doesn't
> work.
> Please tell me how can I solve my problem.
> Regards
> Ali
It sounds to me like Delphi is trying to be helpful and wants to generate a
value for each column that you pass in. If that is the case and you are
picking a table to update from Delphi, you may try creating a View in SQL
Server for the table that leaves off the Identity column. Point Delphi at
the View instead and see if that doesn't solve your problem.
Example:
CREATE TABLE Foo (
IDCol int IDENTITY(1, 1) NOT NULL,
FirstName varchar(20) NULL,
LastName varchar(20) NOT NULL
SSN varchar(9) NOT NULL
)
CREATE VIEW vFoo AS
SELECT FirstName, LastName, SSN FROM Foo
Rick Sawtell
MCT, MCSD, MCDBA|||Instead of accessing your tables from applications directly, make it a
standard practice to use stored procedures.
When in doubt search for 'procedure' in this newsgroup. And of course Books
Online will provide lots of help.
ML
I have a database that it have and int type with identity (1,1).when I add
data from sql everything is ok but when I want to add data from fro example
Delphi it says that field ID must have a value and auto numbering doesn't
work.
Please tell me how can I solve my problem.
Regards
Ali"Ali.M" <AliM@.discussions.microsoft.com> wrote in message
news:4D01366E-E296-4C7E-84FF-58EA93329088@.microsoft.com...
> Hi,
> I have a database that it have and int type with identity (1,1).when I add
> data from sql everything is ok but when I want to add data from fro
example
> Delphi it says that field ID must have a value and auto numbering doesn't
> work.
> Please tell me how can I solve my problem.
> Regards
> Ali
It sounds to me like Delphi is trying to be helpful and wants to generate a
value for each column that you pass in. If that is the case and you are
picking a table to update from Delphi, you may try creating a View in SQL
Server for the table that leaves off the Identity column. Point Delphi at
the View instead and see if that doesn't solve your problem.
Example:
CREATE TABLE Foo (
IDCol int IDENTITY(1, 1) NOT NULL,
FirstName varchar(20) NULL,
LastName varchar(20) NOT NULL
SSN varchar(9) NOT NULL
)
CREATE VIEW vFoo AS
SELECT FirstName, LastName, SSN FROM Foo
Rick Sawtell
MCT, MCSD, MCDBA|||Instead of accessing your tables from applications directly, make it a
standard practice to use stored procedures.
When in doubt search for 'procedure' in this newsgroup. And of course Books
Online will provide lots of help.
ML
Sunday, February 12, 2012
Adding another step to the following
I have the following statement :
CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
CONVERT (VARCHAR(4), SUBSTRING(TerminatingIDDDCityCode, PATINDEX('%[^0]%',
TerminatingIDDDCityCode), LEN(TerminatingIDDDCityCode)))))
This runs perfectly.
However I just found out that the field actually contains two different
"types" of data. Example of the two data entries in the field are:
00000011 or
00000-12
What I need to add to the above statement is that if the data in the field
contain the '-', I need for it to be changed to a zero (0) and keep it so
that the results would look like
11 or
012
Thanks in advance... Will post sample data and table if neededOn Wed, 26 Jan 2005 08:07:04 -0800, scuba79 wrote:
(snip)
>What I need to add to the above statement is that if the data in the field
>contain the '-', I need for it to be changed to a zero (0) and keep it so
>that the results would look like
(snip)
Hi scuba79,
You can change any '-' to '0' with
REPLACE (terminatingcountrycode, '-', '0')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try,
select
case when patindex('%[^0-9]%', colA) > 0 then '0' + right(colA,
patindex('%[^0-9]%', reverse(colA)) - 1)
else cast(cast(colA as int) as varchar)
end
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"scuba79" wrote:
> I have the following statement :
> CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
> PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
> CONVERT (VARCHAR(4), SUBSTRING(TerminatingIDDDCityCode, PATINDEX('%[^0]%',
> TerminatingIDDDCityCode), LEN(TerminatingIDDDCityCode)))))
> This runs perfectly.
> However I just found out that the field actually contains two different
> "types" of data. Example of the two data entries in the field are:
> 00000011 or
> 00000-12
> What I need to add to the above statement is that if the data in the field
> contain the '-', I need for it to be changed to a zero (0) and keep it so
> that the results would look like
> 11 or
> 012
> Thanks in advance... Will post sample data and table if needed
>|||Or,
select
case when charindex('-', colA) > 0 then '0' + right(colA, charindex('-',
reverse(colA)) - 1)
else cast(cast(colA as int) as varchar)
end
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"Alejandro Mesa" wrote:
> Try,
> select
> case when patindex('%[^0-9]%', colA) > 0 then '0' + right(colA,
> patindex('%[^0-9]%', reverse(colA)) - 1)
> else cast(cast(colA as int) as varchar)
> end
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
>
> "scuba79" wrote:
>|||Or,
select
replace(coalesce(nullif(right(colA, charindex('-', reverse(colA))), ''),
cast(cast(colA as int) as varchar)), '-', '0')
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"Alejandro Mesa" wrote:
> Or,
> select
> case when charindex('-', colA) > 0 then '0' + right(colA, charindex('-',
> reverse(colA)) - 1)
> else cast(cast(colA as int) as varchar)
> end
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
> "Alejandro Mesa" wrote:
>|||Alejandro,
Your statement works great when I run it as a seperate statement and not
combined with the rest of the statement that I provided. I mistated my
question... The statement shows that there are two different fields being
changed. However, it's the field "terminatingidddcitycode" that has the two
different data entries that I need to find the solution for while still bein
g
able to use
"CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+"
part, since that field will never contain any "-". Unless I totaling losing
it and not understanding your code
Thanks
scuba79
"Alejandro Mesa" wrote:
> Or,
> select
> replace(coalesce(nullif(right(colA, charindex('-', reverse(colA))), ''),
> cast(cast(colA as int) as varchar)), '-', '0')
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
>
> "Alejandro Mesa" wrote:
>|||Can you provide some DDL (for those columns), sample data and expected resul
t?
AMB
"scuba79" wrote:
> Alejandro,
> Your statement works great when I run it as a seperate statement and not
> combined with the rest of the statement that I provided. I mistated my
> question... The statement shows that there are two different fields being
> changed. However, it's the field "terminatingidddcitycode" that has the t
wo
> different data entries that I need to find the solution for while still be
ing
> able to use
> "CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
> PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
"
> part, since that field will never contain any "-". Unless I totaling losi
ng
> it and not understanding your code
> Thanks
> scuba79
> "Alejandro Mesa" wrote:
>|||On Wed, 26 Jan 2005 08:57:01 -0800, scuba79 wrote:
>Your statement works great when I run it as a seperate statement and not
>combined with the rest of the statement that I provided. I mistated my
>question... The statement shows that there are two different fields being
>changed. However, it's the field "terminatingidddcitycode" that has the tw
o
>different data entries that I need to find the solution for while still bei
ng
>able to use
>"CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
>PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+"
>part, since that field will never contain any "-". Unless I totaling losin
g
>it and not understanding your code
Hi scuba79,
If you take your original query and change each terminatingidddcitycode to
REPLACE (terminatingidddcitycode, '-','0'), it should work.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
By using the replace statement as you stated. When the entire statement
runs won't it strip that zero (0) '
Scuba79
"Hugo Kornelis" wrote:
> On Wed, 26 Jan 2005 08:57:01 -0800, scuba79 wrote:
>
> Hi scuba79,
> If you take your original query and change each terminatingidddcitycode to
> REPLACE (terminatingidddcitycode, '-','0'), it should work.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Alejandro using the following:
CREATE TABLE [Table2] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[TerminatingCountryCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OriginatingCountryCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[TerminatingIDDDCityCode] [varchar] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[OriginatingIDDDCityCode] [varchar] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0052', '0000', '00000002','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0052', '0000', '00000-68','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '007', '0000', '00000003','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0079', '0000', '00000-79','00000000')
The results that I looking for are when you combine fields
TerminatingCountryCode and TerminatingIDDDCityCode should be
522
52068
73
79079
Hope this helps and thank you for the assistance
Scuba79
"Alejandro Mesa" wrote:
> Can you provide some DDL (for those columns), sample data and expected res
ult?
>
> AMB
> "scuba79" wrote:
>
CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
CONVERT (VARCHAR(4), SUBSTRING(TerminatingIDDDCityCode, PATINDEX('%[^0]%',
TerminatingIDDDCityCode), LEN(TerminatingIDDDCityCode)))))
This runs perfectly.
However I just found out that the field actually contains two different
"types" of data. Example of the two data entries in the field are:
00000011 or
00000-12
What I need to add to the above statement is that if the data in the field
contain the '-', I need for it to be changed to a zero (0) and keep it so
that the results would look like
11 or
012
Thanks in advance... Will post sample data and table if neededOn Wed, 26 Jan 2005 08:07:04 -0800, scuba79 wrote:
(snip)
>What I need to add to the above statement is that if the data in the field
>contain the '-', I need for it to be changed to a zero (0) and keep it so
>that the results would look like
(snip)
Hi scuba79,
You can change any '-' to '0' with
REPLACE (terminatingcountrycode, '-', '0')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try,
select
case when patindex('%[^0-9]%', colA) > 0 then '0' + right(colA,
patindex('%[^0-9]%', reverse(colA)) - 1)
else cast(cast(colA as int) as varchar)
end
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"scuba79" wrote:
> I have the following statement :
> CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
> PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
> CONVERT (VARCHAR(4), SUBSTRING(TerminatingIDDDCityCode, PATINDEX('%[^0]%',
> TerminatingIDDDCityCode), LEN(TerminatingIDDDCityCode)))))
> This runs perfectly.
> However I just found out that the field actually contains two different
> "types" of data. Example of the two data entries in the field are:
> 00000011 or
> 00000-12
> What I need to add to the above statement is that if the data in the field
> contain the '-', I need for it to be changed to a zero (0) and keep it so
> that the results would look like
> 11 or
> 012
> Thanks in advance... Will post sample data and table if needed
>|||Or,
select
case when charindex('-', colA) > 0 then '0' + right(colA, charindex('-',
reverse(colA)) - 1)
else cast(cast(colA as int) as varchar)
end
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"Alejandro Mesa" wrote:
> Try,
> select
> case when patindex('%[^0-9]%', colA) > 0 then '0' + right(colA,
> patindex('%[^0-9]%', reverse(colA)) - 1)
> else cast(cast(colA as int) as varchar)
> end
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
>
> "scuba79" wrote:
>|||Or,
select
replace(coalesce(nullif(right(colA, charindex('-', reverse(colA))), ''),
cast(cast(colA as int) as varchar)), '-', '0')
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"Alejandro Mesa" wrote:
> Or,
> select
> case when charindex('-', colA) > 0 then '0' + right(colA, charindex('-',
> reverse(colA)) - 1)
> else cast(cast(colA as int) as varchar)
> end
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
> "Alejandro Mesa" wrote:
>|||Alejandro,
Your statement works great when I run it as a seperate statement and not
combined with the rest of the statement that I provided. I mistated my
question... The statement shows that there are two different fields being
changed. However, it's the field "terminatingidddcitycode" that has the two
different data entries that I need to find the solution for while still bein
g
able to use
"CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+"
part, since that field will never contain any "-". Unless I totaling losing
it and not understanding your code
Thanks
scuba79
"Alejandro Mesa" wrote:
> Or,
> select
> replace(coalesce(nullif(right(colA, charindex('-', reverse(colA))), ''),
> cast(cast(colA as int) as varchar)), '-', '0')
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
>
> "Alejandro Mesa" wrote:
>|||Can you provide some DDL (for those columns), sample data and expected resul
t?
AMB
"scuba79" wrote:
> Alejandro,
> Your statement works great when I run it as a seperate statement and not
> combined with the rest of the statement that I provided. I mistated my
> question... The statement shows that there are two different fields being
> changed. However, it's the field "terminatingidddcitycode" that has the t
wo
> different data entries that I need to find the solution for while still be
ing
> able to use
> "CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
> PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
"
> part, since that field will never contain any "-". Unless I totaling losi
ng
> it and not understanding your code
> Thanks
> scuba79
> "Alejandro Mesa" wrote:
>|||On Wed, 26 Jan 2005 08:57:01 -0800, scuba79 wrote:
>Your statement works great when I run it as a seperate statement and not
>combined with the rest of the statement that I provided. I mistated my
>question... The statement shows that there are two different fields being
>changed. However, it's the field "terminatingidddcitycode" that has the tw
o
>different data entries that I need to find the solution for while still bei
ng
>able to use
>"CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
>PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+"
>part, since that field will never contain any "-". Unless I totaling losin
g
>it and not understanding your code
Hi scuba79,
If you take your original query and change each terminatingidddcitycode to
REPLACE (terminatingidddcitycode, '-','0'), it should work.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
By using the replace statement as you stated. When the entire statement
runs won't it strip that zero (0) '
Scuba79
"Hugo Kornelis" wrote:
> On Wed, 26 Jan 2005 08:57:01 -0800, scuba79 wrote:
>
> Hi scuba79,
> If you take your original query and change each terminatingidddcitycode to
> REPLACE (terminatingidddcitycode, '-','0'), it should work.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Alejandro using the following:
CREATE TABLE [Table2] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[TerminatingCountryCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OriginatingCountryCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[TerminatingIDDDCityCode] [varchar] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[OriginatingIDDDCityCode] [varchar] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0052', '0000', '00000002','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0052', '0000', '00000-68','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '007', '0000', '00000003','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0079', '0000', '00000-79','00000000')
The results that I looking for are when you combine fields
TerminatingCountryCode and TerminatingIDDDCityCode should be
522
52068
73
79079
Hope this helps and thank you for the assistance
Scuba79
"Alejandro Mesa" wrote:
> Can you provide some DDL (for those columns), sample data and expected res
ult?
>
> AMB
> "scuba79" wrote:
>
Adding an int to a datetime type as minutes pleasse help!
Hi,
I'm trying to add an int type to a datetime type to produce a
datatime type that interprets the int as *minutes*, for example:
2008-03-20 15:36:09.920 + 10 = 2008-03-20 15:46:09.920
Problem is though - I'm stuck. Whenever I try to do this SQL Server
2005 interprets the "int" as days and I get the wrong answer. Can
ayone help me please? Any comments/suggestions/code-samples much, much
appreciated.
Thank,
Al.
Use the DATEADD function. Implicit DATETIME math is always in number of
days.
SELECT DATEADD(MINUTE, 10, '2008-03-20 15:36:09.920');
While the default is not entirely intuitive, why would you assume that SQL
Server will know that when you typed "10" you meant minutes? What if I did
the same, and expected seconds, and my co-worker expects months? SQL Server
can't be psychic...
A
<almurph@.altavista.com> wrote in message
news:36124fcf-6486-43cc-8297-90972b9d9817@.h11g2000prf.googlegroups.com...
> Hi,
> I'm trying to add an int type to a datetime type to produce a
> datatime type that interprets the int as *minutes*, for example:
> 2008-03-20 15:36:09.920 + 10 = 2008-03-20 15:46:09.920
>
> Problem is though - I'm stuck. Whenever I try to do this SQL Server
> 2005 interprets the "int" as days and I get the wrong answer. Can
> ayone help me please? Any comments/suggestions/code-samples much, much
> appreciated.
> Thank,
> Al.
|||On Mar 20, 3:55Xpm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Use the DATEADD function. XImplicit DATETIME math is always in number of
> days.
> SELECT DATEADD(MINUTE, 10, '2008-03-20 15:36:09.920');
> While the default is not entirely intuitive, why would you assume that SQL
> Server will know that when you typed "10" you meant minutes? XWhat if I did
> the same, and expected seconds, and my co-worker expects months? XSQL Server
> can't be psychic...
> A
> <almu...@.altavista.com> wrote in message
> news:36124fcf-6486-43cc-8297-90972b9d9817@.h11g2000prf.googlegroups.com...
>
>
>
>
> - Show quoted text -
Aaron,
Thank you very much.
Al.
I'm trying to add an int type to a datetime type to produce a
datatime type that interprets the int as *minutes*, for example:
2008-03-20 15:36:09.920 + 10 = 2008-03-20 15:46:09.920
Problem is though - I'm stuck. Whenever I try to do this SQL Server
2005 interprets the "int" as days and I get the wrong answer. Can
ayone help me please? Any comments/suggestions/code-samples much, much
appreciated.
Thank,
Al.
Use the DATEADD function. Implicit DATETIME math is always in number of
days.
SELECT DATEADD(MINUTE, 10, '2008-03-20 15:36:09.920');
While the default is not entirely intuitive, why would you assume that SQL
Server will know that when you typed "10" you meant minutes? What if I did
the same, and expected seconds, and my co-worker expects months? SQL Server
can't be psychic...
A
<almurph@.altavista.com> wrote in message
news:36124fcf-6486-43cc-8297-90972b9d9817@.h11g2000prf.googlegroups.com...
> Hi,
> I'm trying to add an int type to a datetime type to produce a
> datatime type that interprets the int as *minutes*, for example:
> 2008-03-20 15:36:09.920 + 10 = 2008-03-20 15:46:09.920
>
> Problem is though - I'm stuck. Whenever I try to do this SQL Server
> 2005 interprets the "int" as days and I get the wrong answer. Can
> ayone help me please? Any comments/suggestions/code-samples much, much
> appreciated.
> Thank,
> Al.
|||On Mar 20, 3:55Xpm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Use the DATEADD function. XImplicit DATETIME math is always in number of
> days.
> SELECT DATEADD(MINUTE, 10, '2008-03-20 15:36:09.920');
> While the default is not entirely intuitive, why would you assume that SQL
> Server will know that when you typed "10" you meant minutes? XWhat if I did
> the same, and expected seconds, and my co-worker expects months? XSQL Server
> can't be psychic...
> A
> <almu...@.altavista.com> wrote in message
> news:36124fcf-6486-43cc-8297-90972b9d9817@.h11g2000prf.googlegroups.com...
>
>
>
>
> - Show quoted text -
Aaron,
Thank you very much.
Al.
Adding an int to a datetime type as minutes pleasse help!
Hi,
I'm trying to add an int type to a datetime type to produce a
datatime type that interprets the int as *minutes*, for example:
2008-03-20 15:36:09.920 + 10 = 2008-03-20 15:46:09.920
Problem is though - I'm stuck. Whenever I try to do this SQL Server
2005 interprets the "int" as days and I get the wrong answer. Can
ayone help me please? Any comments/suggestions/code-samples much, much
appreciated.
Thank,
Al.Use the DATEADD function. Implicit DATETIME math is always in number of
days.
SELECT DATEADD(MINUTE, 10, '2008-03-20 15:36:09.920');
While the default is not entirely intuitive, why would you assume that SQL
Server will know that when you typed "10" you meant minutes? What if I did
the same, and expected seconds, and my co-worker expects months? SQL Server
can't be psychic...
A
<almurph@.altavista.com> wrote in message
news:36124fcf-6486-43cc-8297-90972b9d9817@.h11g2000prf.googlegroups.com...
> Hi,
> I'm trying to add an int type to a datetime type to produce a
> datatime type that interprets the int as *minutes*, for example:
> 2008-03-20 15:36:09.920 + 10 = 2008-03-20 15:46:09.920
>
> Problem is though - I'm stuck. Whenever I try to do this SQL Server
> 2005 interprets the "int" as days and I get the wrong answer. Can
> ayone help me please? Any comments/suggestions/code-samples much, much
> appreciated.
> Thank,
> Al.|||On Mar 20, 3:55=A0pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Use the DATEADD function. =A0Implicit DATETIME math is always in number of=
> days.
> SELECT DATEADD(MINUTE, 10, '2008-03-20 15:36:09.920');
> While the default is not entirely intuitive, why would you assume that SQL=
> Server will know that when you typed "10" you meant minutes? =A0What if I =did
> the same, and expected seconds, and my co-worker expects months? =A0SQL Se=rver
> can't be psychic...
> A
> <almu...@.altavista.com> wrote in message
> news:36124fcf-6486-43cc-8297-90972b9d9817@.h11g2000prf.googlegroups.com...
>
> > Hi,
> > I'm trying to add an int type to a datetime type to produce a
> > datatime type that interprets the int as *minutes*, for example:
> > 2008-03-20 15:36:09.920 + 10 =3D 2008-03-20 15:46:09.920
> > Problem is though - I'm stuck. Whenever I try to do this SQL Server
> > 2005 interprets the "int" as days and I get the wrong answer. Can
> > ayone help me please? Any comments/suggestions/code-samples much, much
> > appreciated.
> > Thank,
> > Al.- Hide quoted text -
> - Show quoted text -
Aaron,
Thank you very much.
Al.
I'm trying to add an int type to a datetime type to produce a
datatime type that interprets the int as *minutes*, for example:
2008-03-20 15:36:09.920 + 10 = 2008-03-20 15:46:09.920
Problem is though - I'm stuck. Whenever I try to do this SQL Server
2005 interprets the "int" as days and I get the wrong answer. Can
ayone help me please? Any comments/suggestions/code-samples much, much
appreciated.
Thank,
Al.Use the DATEADD function. Implicit DATETIME math is always in number of
days.
SELECT DATEADD(MINUTE, 10, '2008-03-20 15:36:09.920');
While the default is not entirely intuitive, why would you assume that SQL
Server will know that when you typed "10" you meant minutes? What if I did
the same, and expected seconds, and my co-worker expects months? SQL Server
can't be psychic...
A
<almurph@.altavista.com> wrote in message
news:36124fcf-6486-43cc-8297-90972b9d9817@.h11g2000prf.googlegroups.com...
> Hi,
> I'm trying to add an int type to a datetime type to produce a
> datatime type that interprets the int as *minutes*, for example:
> 2008-03-20 15:36:09.920 + 10 = 2008-03-20 15:46:09.920
>
> Problem is though - I'm stuck. Whenever I try to do this SQL Server
> 2005 interprets the "int" as days and I get the wrong answer. Can
> ayone help me please? Any comments/suggestions/code-samples much, much
> appreciated.
> Thank,
> Al.|||On Mar 20, 3:55=A0pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Use the DATEADD function. =A0Implicit DATETIME math is always in number of=
> days.
> SELECT DATEADD(MINUTE, 10, '2008-03-20 15:36:09.920');
> While the default is not entirely intuitive, why would you assume that SQL=
> Server will know that when you typed "10" you meant minutes? =A0What if I =did
> the same, and expected seconds, and my co-worker expects months? =A0SQL Se=rver
> can't be psychic...
> A
> <almu...@.altavista.com> wrote in message
> news:36124fcf-6486-43cc-8297-90972b9d9817@.h11g2000prf.googlegroups.com...
>
> > Hi,
> > I'm trying to add an int type to a datetime type to produce a
> > datatime type that interprets the int as *minutes*, for example:
> > 2008-03-20 15:36:09.920 + 10 =3D 2008-03-20 15:46:09.920
> > Problem is though - I'm stuck. Whenever I try to do this SQL Server
> > 2005 interprets the "int" as days and I get the wrong answer. Can
> > ayone help me please? Any comments/suggestions/code-samples much, much
> > appreciated.
> > Thank,
> > Al.- Hide quoted text -
> - Show quoted text -
Aaron,
Thank you very much.
Al.
Subscribe to:
Posts (Atom)