Create Table from Row Data

Hello,
In SQL Server 2000, is it possible to take a table with one field (column), and pivot the table so that the characters in the row data become the field (column) names of another table ( or in a View)? The number of records could vary.

If so, how would I do this?

Sample table;
Create Table dbo.MonthlyData
(
Categories varchar(30) NOT NULL
)

Sample data;

Sales Volume 2005-02
TotRefVol 2005-02
Sales Ratio 2005-02
Sales Volume 2005-03
TotRefVol 2005-03
Sales Ratio 2005-03
Sales Volume 2005-04
TotRefVol 2005-04
Sales Ratio 2005-04

If I am following you correctly; Are you wanting Sales Volume,etc.. to be a column name in a view or table?|||

First; Sorry, the subject should have been 'Create table fields from row data'. To answer your question, each row of sample data is contained within a single column called 'Categories'.

|||

Assuming you had some other value to use with the columns you reference you could do something like:

Create Table dbo.MonthlyData

(

CategoryID int IDENTITY(1,1)

,Categories varchar(30) NOT NULL

,Value varchar(50)

)

INSERT dbo.MonthlyData (Categories, [Value]) VALUES('Sales Volume 2005-02', 'Small')

INSERT dbo.MonthlyData (Categories, [Value]) VALUES('TotRefVol 2005-02', 'Medium')

INSERT dbo.MonthlyData (Categories, [Value]) VALUES('Sales Ratio 2005-02', 'Large')

INSERT dbo.MonthlyData (Categories, [Value]) VALUES('Sales Volume 2005-03', 'Extra Large')

INSERT dbo.MonthlyData (Categories, [Value]) VALUES('TotRefVol 2005-03', 'Small')

INSERT dbo.MonthlyData (Categories, [Value]) VALUES('Sales Ratio 2005-03', 'Medium')

INSERT dbo.MonthlyData (Categories, [Value]) VALUES('Sales Volume 2005-04', 'Large')

INSERT dbo.MonthlyData (Categories, [Value]) VALUES('TotRefVol 2005-04', 'Extra Large')

INSERT dbo.MonthlyData (Categories, [Value]) VALUES('Sales Ratio 2005-04', 'Small')

DECLARE @.string nvarchar(1000)

SELECT @.string = ISNULL(@.string + ', ', '') + QUOTENAME([Value], '''') + QUOTENAME(Categories)

FROM dbo.MonthlyData

SET @.string = 'SELECT ' + @.string

EXEC sp_executesql @.string

This will give you Categories as your column header with associated Value column.

|||

Thank you for your help, I will try this!

cdun2

|||

Thanks again. I had a couple of questions;

-Procedure sp_executesql expects parameter '@.statement' of type 'ntext/nchar/nvarchar'; nvarchar has a 'size' limit of 4000, and ntext cannot be the datatype of local variable @.string. char will handle up to 8000 characters. How can I work around these limitations?

-Could a table be created from the results of sp_executesql @.string so that the column names become fields in the table?

cdun2

|||

Yes, you could alter the syntax

@.sql = 'select ' + @.sql

to

@.sql = 'select ' + @.sql + ' into myTable'

this would keep the columns dynamic. If the number of columns will be static you could create the table and do the following:

@.sql = 'insert myTable (valuelist) select ' + @.sql

|||I realized I didn't answer the first part of your question. If the string you are trying to pass is greater than the 4000 limit of nvarchar you can replace sp_executesql with exec() and make @.string a varchar(8000). It is better practice to use sp_executesql with dynamic sql but in this case it is your only real option.

Create SQL table from Excel or DataTable?

Hello,

I am trying to create a new table in SQL Server based on an excel sheet someone uploads to my site (ie No DTS, and I don't know the field names). How can I easily do that?

Can I make a sql table based on a DataTable without going row-by-row? Cause then I could go excel to datatable to sql table.

Thanks a bunch,

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=373468&SiteID=1

same thing what you want see last answer

|||

Can someone do this in VB? I can convert a little C#, but don't understand the syntax enough to convert all that.

|||

http://www.kamalpatel.net/ConvertCSharp2VB.aspx

Create query based on a field that wont be the same value in both tables

I have two tables: TestA and TestB. Both tables have 3 fields: ID,
Name, and RunDate. I need to create a query which will join the two
tables first on Name but then I need to match up the RunDates even
though the RunDates won't be the same.

CREATE TABLE TestA (ID INT IDENTITY, Name VARCHAR(255), RunDate
DATETIME)
CREATE TABLE TestB (ID INT IDENTITY, Name VARCHAR(255), RunDate
DATETIME)

INSERT INTO TestA VALUES ('Account 1', '9/1/2004 12:00PM')
INSERT INTO TestB VALUES ('Account 1', '9/1/2004 12:15PM')
INSERT INTO TestA VALUES ('Account 1', '9/2/2004 1:00PM')
INSERT INTO TestB VALUES ('Account 1', '9/2/2004 1:15PM')
INSERT INTO TestA VALUES ('Account 1', '9/3/2004 3:00PM')
INSERT INTO TestA VALUES ('Account 2', '9/5/2004 4:00PM')
INSERT INTO TestB VALUES ('Account 2', '9/5/2004 4:15PM')

Here's a common scenario:
User updates TestA data for Account 1 on 9/1/2004 at 12:00pm. Then
the user updates TestB data for Account 1, 15 minutes later. I want
these two records to match. The user must always update TestA data
before they update TestB data. Therefore, there might be more rows in
TestA then in TestB

Here's what the results should look like for the above data.

Name TestA Date TestB Date
-- ---- ----
Account 1 9/1/2004 12:00pm 9/1/2004 12:15PM
Account 1 9/2/2004 1:00pm 9/2/2004 1:15PM
Account 1 9/3/2004 3:00pm (NULL)
Account 2 9/5/2004 4:00pm 9/5/2004 4:15PM

Any help would be much appreciated!!!!On 29 Sep 2004 07:41:18 -0700, Jim G wrote:

>Here's what the results should look like for the above data.
>Name TestA Date TestB Date
>-- ---- ----
>Account 1 9/1/2004 12:00pm 9/1/2004 12:15PM
>Account 1 9/2/2004 1:00pm 9/2/2004 1:15PM
>Account 1 9/3/2004 3:00pm (NULL)
>Account 2 9/5/2004 4:00pm 9/5/2004 4:15PM

Hi Jim,

Thanks for posting DDL ans INSERTS for sample data!

The following query gives the above results:

SELECT a.Name, a.RunDate, b.RunDate
FROM TestA AS a
LEFT JOIN TestB AS b
ON b.Name = a.Name
AND b.RunDate >= a.RunDate
AND NOT EXISTS (SELECT *
FROM TestA AS a2
WHERE a2.Name = a.Name
AND a2.RunDate > a.RunDate
AND a2.RunDate < b.RunDate)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Awesome! That worked perfectly. Thanks!

Create Primary Key with increment and format?

I have an access table that has a primary key (entitled "ID Number"), no duplicates, the field is an integer.
And, importantly, the value is set to "increment".
The format is "phd"000 - so it starts outphd001,phd002, and so on...
How to do this in an SQL table? Can that format be done? Or is it better not to do it via SQL but in coding instead?In SQL Server you do Unique constraint or Unique index for no duplicates the former allow nulls the later not null being primary key and set IDENTITY property on the column for auto increament. Run a search for Unique constraint and Unique index and the IDENTITY property in SQL Server BOL(books online). Hope this helps.

create other index

Hello:
I create a key in one table, the field is uniqueidentifier, but I search
very frequently for one [date] field, and I like to index this field with
datetime datatype to optimize the search, how can I do that?
Best regards,
Owen."Owen" <anibal@.prensa-latina.cu> wrote in message
news:uxQMR9vEGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello:
> I create a key in one table, the field is uniqueidentifier, but I search
> very frequently for one [date] field, and I like to index this field with
> datetime datatype to optimize the search, how can I do that?
> Best regards,
> Owen.
>
CREATE INDEX <indexname> ON <TableName> (<column1>, <column2>, ...)
Example:
CREATE INDEX IX_Frogs_BirthDate ON Frogs (BirthDate)
As a side note.. If you are frequently searching on a range of dates using
statements like BETWEEN, then you may find a CLUSTERED index on this date
column to be far more effective. Clustered indexes on GUIDs can be clumsy
at best.
Rick Sawtell
MCT, MCSD, MCDBA|||hi, thanks for answer:
the problem is that I need keep this two index on the same table, the guid
and the dates, but only one can be CLUSTERED, how can optimize this to all
index work faster?
Best regards,
Owen.
"Rick Sawtell" <Quickening@.msn.com> wrote in message
news:eWCHVDwEGHA.644@.TK2MSFTNGP09.phx.gbl...
> "Owen" <anibal@.prensa-latina.cu> wrote in message
> news:uxQMR9vEGHA.1032@.TK2MSFTNGP11.phx.gbl...
with
> CREATE INDEX <indexname> ON <TableName> (<column1>, <column2>, ...)
> Example:
> CREATE INDEX IX_Frogs_BirthDate ON Frogs (BirthDate)
>
> As a side note.. If you are frequently searching on a range of dates
using
> statements like BETWEEN, then you may find a CLUSTERED index on this date
> column to be far more effective. Clustered indexes on GUIDs can be
clumsy
> at best.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Owen as you say only one index can be clustered, but one of the significant
benefits of a clustered index is for querioes based on a range of values
since all those rows will be found near each other (all else being equal).
Now a GUID is *extremely unlikely* (probably nearly safe to say never)
likely to be used in a query like
WHERE guidcol between 'yuckyguidvalue1' and 'yuckyguidvalue2'
So makeing the CI on the date will have the benefit (if you retrieve rows
based on a date range) of being faster than retrieving the same range when
the supporting index is non-clustered. Hence Rick's suggestion that you
consider making the CI on the date column.
To go much further needs an understanding of the type of queries on the
table, and the approx size of it, and is frequently best checked by running
testst on your configuration.
You may have guessed I fall in to the camp of disliking guids for any sort
of identifiers unless there is an absolute cast-iron reason for needing them
(like a distributed db that has to have surrogate keys generated uniquely,
or complex replication) .
Mike John
"Owen" <anibal@.prensa-latina.cu> wrote in message
news:eNcVeIwEGHA.3064@.TK2MSFTNGP10.phx.gbl...
> hi, thanks for answer:
> the problem is that I need keep this two index on the same table, the guid
> and the dates, but only one can be CLUSTERED, how can optimize this to all
> index work faster?
> Best regards,
> Owen.
>
> "Rick Sawtell" <Quickening@.msn.com> wrote in message
> news:eWCHVDwEGHA.644@.TK2MSFTNGP09.phx.gbl...
> with
> using
> clumsy
>|||Just don't forget that the GUID may be an FK where the "=" searches would
return many rows. In that case depending on how many queries against each
column and what columns you fetch we still may consider using the GUID as
the clustered index.
/ Tobias|||True - apologies - i was falling into an assumption that the guid was going
to be unique!
Mike
"Tobias Thernstrm" <ttnospam@.rbam.se> wrote in message
news:O%23qfmNCFGHA.3632@.TK2MSFTNGP10.phx.gbl...
> Just don't forget that the GUID may be an FK where the "=" searches would
> return many rows. In that case depending on how many queries against each
> column and what columns you fetch we still may consider using the GUID as
> the clustered index.
> / Tobias
>

Create Non-Sequential Unique ID (EAN?)

Hi all,

I might be getting this all wrong but bear with me.

I need to create some kind of Unique field in my DB that isnonsequential. This is because I need it to be difficult to guessids if you have an example in front of you.

I have looked at8digit EAN codes which include a check digit system.( I use a base digit of the row_id for these) Can anyone tell mehow many uniques I can get out of this system?

For my ID: I have looked at something along the lines of:

Hex(row_id) + "T" + Hex( Trimmed(EAN)) The "T" serves to split the numbers for when I am converting back.

So for example:

row_id EAN_code Hex(row_id) + "T" + Hex( Trimmed(EAN) )
---------------------------
3166 00031663 C5ET7BAF
3167 00031673 C5FT7BB9
3168 00031686 C60T7BC6

Is this too easy to guess (once you can tell there are two hex numbers there?)

What do people think?

Thanks,

Pete

Hi Pete,

Based on your description, I understand that you need a field in the databasae table, which is generated automatically. It has to be unique and non-sequential.

In this case, I would suggest the UniqueIdentifier data type. It is a GUID value and can be generated with NEWID() function.

Here are some more information about this data type:

http://msdn2.microsoft.com/en-us/library/ms187942.aspx

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

|||

Hi Kevin,

Sorry, once again I have forgotten to post somepretty vital information! However, I didnt know about SQLGUID so thanks anyway!

The limitation on the ID I requireis that it needs to be typed into a text message (SMS) so I'm trying tokeep it as short as possible.

Does anyone know of any suchnumbering systems? I think anything between 8-14 chars would beperfect. (I realise that we may have anything up to amillion records so the number of chars in the ID will go up - or atleast - Any leading zeros will be removed)

Thanks again,

Pete

|||

Hi Pete,

Sorry for my ignorance, but I don't know any ID system like that. May the the other community member has some idea on this.

|||Cheers Kevin, maybe I'm just looking for a Silver Bullet!

create linefeed in field

Hello,

I would like to create more lines by concatenating values.
When I use: <select 'This' + ' ' + 'is' + ' ' + 'an' + ' ' +
'example'> the result is <This is an example> (on the same line).
I woul like to get:
<This
is
an
example> (each 'word' on a new line, but in 1 field)
Whis SQL statement do i have to use?"Hans" <hans.de.korte@.prominent.nl> wrote in message
news:ae7dcba4.0402200557.1942ab24@.posting.google.c om...
> Hello,
> I would like to create more lines by concatenating values.
> When I use: <select 'This' + ' ' + 'is' + ' ' + 'an' + ' ' +
> 'example'> the result is <This is an example> (on the same line).
> I woul like to get:
> <This
> is
> an
> example> (each 'word' on a new line, but in 1 field)
> Whis SQL statement do i have to use?

See CHAR() in Books Online.

Simon|||It looks like you are trying to format a string in SQL. It is always a
good practice to do this kind of formatting in the application. Given
that in order to add a line break as part of the string you need to
use the char function. char(13)+char(10) make a line break i.e. line
feed and carriage return. To answer your example you can try
declare @.cf varchar(2)
set @.cf=' '+char(13)+char(10)+' '
select 'This' + @.cr + 'is' + @.cf + 'an' + @.cf + 'example'

If you don't want to use the variable then you substitute the variable
with the expression ' '+char(13)+char(10)+' '. As I mentioned before
it is not a best practice to do this kind of formatting at database
level.

Ramesh

hans.de.korte@.prominent.nl (Hans) wrote in message news:<ae7dcba4.0402200557.1942ab24@.posting.google.com>...
> Hello,
> I would like to create more lines by concatenating values.
> When I use: <select 'This' + ' ' + 'is' + ' ' + 'an' + ' ' +
> 'example'> the result is <This is an example> (on the same line).
> I woul like to get:
> <This
> is
> an
> example> (each 'word' on a new line, but in 1 field)
> Whis SQL statement do i have to use?

create linefeed in field

Hello,
I would like to create more lines by concatenating values.
When I use: <select 'This' + ' ' + 'is' + ' ' + 'an' + ' ' + 'example'> the result is <This is an example> (on the same line).
I woul like to get:
<This
is
an
example> (each 'word' on a new line, but in 1 field)
Whis SQL statement do i have to use?SELECT 'This' + ' ' + CHAR(10) + 'is' + CHAR(10) + ' ' + 'an' + CHAR(10) + '
' + 'example'
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Hans de Korte" <hans.de.korte@.prominent.nl> wrote in message
news:06D0883E-6C6A-48F5-8205-58FA1B716BDF@.microsoft.com...
> Hello,
> I would like to create more lines by concatenating values.
> When I use: <select 'This' + ' ' + 'is' + ' ' + 'an' + ' ' + 'example'>
the result is <This is an example> (on the same line).
> I woul like to get:
> <This
> is
> an
> example> (each 'word' on a new line, but in 1 field)
> Whis SQL statement do i have to use?|||You can use carriage return (CHAR(13)) and line feed (CHAR(10)) separators:
DECLARE @.CrLF AS char(2)
SET @.CrLF = CHAR(13) + CHAR(13)
SELECT 'This' + @.CrLF +
'is' + @.CrLF +
'an' + @.CrLF +
'example'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hans de Korte" <hans.de.korte@.prominent.nl> wrote in message
news:06D0883E-6C6A-48F5-8205-58FA1B716BDF@.microsoft.com...
> Hello,
> I would like to create more lines by concatenating values.
> When I use: <select 'This' + ' ' + 'is' + ' ' + 'an' + ' ' + 'example'>
the result is <This is an example> (on the same line).
> I woul like to get:
> <This
> is
> an
> example> (each 'word' on a new line, but in 1 field)
> Whis SQL statement do i have to use?|||Thanks, I will try
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

create linefeed in field

Hello,
I would like to create more lines by concatenating values.
When I use: <select 'This' + ' ' + 'is' + ' ' + 'an' + ' ' + 'example'> the
result is <This is an example> (on the same line).
I woul like to get:
<This
is
an
example> (each 'word' on a new line, but in 1 field)
Whis SQL statement do i have to use?SELECT 'This' + ' ' + CHAR(10) + 'is' + CHAR(10) + ' ' + 'an' + CHAR(10) + '
' + 'example'
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Hans de Korte" <hans.de.korte@.prominent.nl> wrote in message
news:06D0883E-6C6A-48F5-8205-58FA1B716BDF@.microsoft.com...
> Hello,
> I would like to create more lines by concatenating values.
> When I use: <select 'This' + ' ' + 'is' + ' ' + 'an' + ' ' + 'example'>
the result is <This is an example> (on the same line).
> I woul like to get:
> <This
> is
> an
> example> (each 'word' on a new line, but in 1 field)
> Whis SQL statement do i have to use?|||You can use carriage return (CHAR(13)) and line feed (CHAR(10)) separators:
DECLARE @.CrLF AS char(2)
SET @.CrLF = CHAR(13) + CHAR(13)
SELECT 'This' + @.CrLF +
'is' + @.CrLF +
'an' + @.CrLF +
'example'
Hope this helps.
Dan Guzman
SQL Server MVP
"Hans de Korte" <hans.de.korte@.prominent.nl> wrote in message
news:06D0883E-6C6A-48F5-8205-58FA1B716BDF@.microsoft.com...
> Hello,
> I would like to create more lines by concatenating values.
> When I use: <select 'This' + ' ' + 'is' + ' ' + 'an' + ' ' + 'example'>
the result is <This is an example> (on the same line).
> I woul like to get:
> <This
> is
> an
> example> (each 'word' on a new line, but in 1 field)
> Whis SQL statement do i have to use?|||Thanks, I will try
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

create index

I need to check if an index available on table T1 and field F1. If not, create a non-clustered on F1. How can I do this in a stored procedure?

Something like this:

SELECTsc.name,*FROMsysindexkeys skJOINsyscolumns scON sk.colid = sc.colidand sk.id = sc.idWHEREsk.id =object_id('Activity')AND sc.name ='activityid'

create index

I need to check if an index available on table T1 and field F1. If not, create a non-clustered on F1. How can I do this in a stored procedure?

SELECT

C.[Name] AS [IndexedColumns]

FROM sys.indexes I

INNER JOIN sys.index_columns IC ON (I.index_id = IC.index_ID)

INNER JOIN sys.columns C ON (IC.column_ID = C.column_ID)

WHERE

OBJECT_NAME(I.OBJECT_ID) = 'StoreContact'

|||

Actually it is a bit more than that. There are a few things you have to take care of, including included columns. This query will do it:

use tempdb
go
drop table t1
go
create table T1
(
T1Id int primary key,
F1 int,
F2 int,
F3 int,
F4 int
)
create index T1_F2 on t1(f2)
create index T1_F3andF4 on t1(f3,f4)
create index T1_F1includeF4 on t1(f1) include (f4)
go

--the first subquery gets the primary columns in the index. So set the columns
--you want to match in the IN clause. Then set the number to match the number
--of matches you desire.

--the second does the included columns, something you have to consider for 2005
--because it could look like you have an index that you don't actually have if
-- create index T1_F1includeF4 on t1(f1) include (f4)
--is created instead of
--create index T1_F1includeF4 on t1(f1,f4)

select i.name, type_desc,is_unique
from sys.indexes as i
where object_name(i.object_id) = 'T1'
and (select count(*)
from sys.columns as sc
join sys.index_columns as ic
on sc.object_id = ic.object_id
and sc.column_id = ic.column_id
and ic.is_included_column = 0
where i.object_id = ic.object_id
and i.index_id = ic.index_id
and sc.name in ('f1')) = 2 --match # of cols in in clause
and (select count(*)
from sys.columns as sc
join sys.index_columns as ic
on sc.object_id = ic.object_id
and sc.column_id = ic.column_id
and ic.is_included_column = 1
where i.object_id = ic.object_id
and i.index_id = ic.index_id
and sc.name in ('')) = 0 --match # of cols in in clause
go

|||

Jim, if your application is large enough you will soon run into maintainance nightmare if you are not sure whether you have the specific index on specific table. Checking whether the index exists or not and creating it is one-time solution.

Create some table that tracks your database version. If you need to do any schema change, change the db version correspondingly.

For a fixed db version, your table schemas should be unambigous. You might have an sql file for each table, which should include the create table command AND create index commands. Say, if you have added an index(iDATE) for table T on version 6.35, then all the clients that have db version 6.35 should have iDATE on T, and NONE should have such index if their db version is < 6.35.
If you are interested, post your application specifics, and we could discuss specific version changing schemas.
Good luck.

|||

Jim, if your application is large enough you will soon run into maintainance nightmare if you are not sure whether you have the specific index on specific table. Checking whether the index exists or not and creating it is one-time solution.

No doubt. I use this kind of code from my data modeling tool to create indexes if they are on the model but not in the actual database.

Create date field from substring of text field

I am trying to populate a field in a SQL table based on the values
returned from using substring on a text field.

Example:

Field Name = RecNum
Field Value = 024071023

The 7th and 8th character of this number is the year. I am able to
get those digits by saying substring(recnum,7,2) and I get '02'. Now
what I need to do is determine if this is >= 50 then concatenate a
'19' to the front of it or if it is less that '50' concatenate a '20'.
This particular example should return '2002'. Then I want to take the
result of this and populate a field called TaxYear.

Any help would be greatly apprecaietd.

MarkMark,

Assuming both RecNum and TaxYear fields are in the same table, you can use
this script to populate TaxYear:

update YourTable
set TaxYear = case
when SubString(RecNum,7,2) >= '50' then '19' +
SubString(RecNum,7,2)
else '20' + SubString(RecNum,7,2)
end

Shervin

"Mark" <markcash@.Hotmail.com> wrote in message
news:57bdc737.0310151257.1dc4d0a9@.posting.google.c om...
> I am trying to populate a field in a SQL table based on the values
> returned from using substring on a text field.
> Example:
> Field Name = RecNum
> Field Value = 024071023
> The 7th and 8th character of this number is the year. I am able to
> get those digits by saying substring(recnum,7,2) and I get '02'. Now
> what I need to do is determine if this is >= 50 then concatenate a
> '19' to the front of it or if it is less that '50' concatenate a '20'.
> This particular example should return '2002'. Then I want to take the
> result of this and populate a field called TaxYear.
> Any help would be greatly apprecaietd.
> Mark|||This work exaclty like I was wanting!!!

Thanks for the advice Shervin!!

Mark

"Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message news:<vorem27pdlp2a9@.corp.supernews.com>...
> Mark,
> Assuming both RecNum and TaxYear fields are in the same table, you can use
> this script to populate TaxYear:
> update YourTable
> set TaxYear = case
> when SubString(RecNum,7,2) >= '50' then '19' +
> SubString(RecNum,7,2)
> else '20' + SubString(RecNum,7,2)
> end
> Shervin
> "Mark" <markcash@.Hotmail.com> wrote in message
> news:57bdc737.0310151257.1dc4d0a9@.posting.google.c om...
> > I am trying to populate a field in a SQL table based on the values
> > returned from using substring on a text field.
> > Example:
> > Field Name = RecNum
> > Field Value = 024071023
> > The 7th and 8th character of this number is the year. I am able to
> > get those digits by saying substring(recnum,7,2) and I get '02'. Now
> > what I need to do is determine if this is >= 50 then concatenate a
> > '19' to the front of it or if it is less that '50' concatenate a '20'.
> > This particular example should return '2002'. Then I want to take the
> > result of this and populate a field called TaxYear.
> > Any help would be greatly apprecaietd.
> > Mark