Generate 8 Character Alphanumeric Sequence

Apr 20, 2015

Requirements
•ALPHANUMERIC FORMAT – > AA00AA00………..ZZ99ZZ99
Last 8 bytes will alternate between 2 byte alpha/2 byte numeric
•Generate from Alphabets – A through Z Numbers -0 to 9
•Generate Unique Sequence (No Duplicates).
•Must Eliminate letters I and O
Output Expected
•AA00AA00………..ZZ99ZZ99
•Using 24 alphabets & 10 digits ,
24*24*10*10*24*24 = 3 317 760 000 records

Below is my Sql Function -

CREATE function [dbo].[SequenceComplexNEW]
(
@Id BIGINT
)
Returns char(8)
AS
BEGIN
DECLARE @OUT AS CHAR(8)--,@Id as Bigint

[Code] ....

View 1 Replies


ADVERTISEMENT

SQL Server 2008 :: Generate 8 Char Alphanumeric Unique Sequence

Apr 20, 2015

GENERATE 8 CHARACTER ALPHANUMERIC SEQUENCES

Requirements
• ALPHANUMERIC FORMAT – > AA00AA00………..ZZ99ZZ99
Last 8 bytes will alternate between 2 byte alpha/2 byte numeric
• Generate from Alphabets – A through Z Numbers -0 to 9
• Generate Unique Sequence (No Duplicates).
• Must Eliminate letters I and O

Output Expected
• AA00AA00………..ZZ99ZZ99
• Using 24 alphabets & 10 digits ,
24*24*10*10*24*24 = 3 317 760 000 records

Below is my Sql Function -

CREATE function [dbo].[SequenceComplexNEW]
(
@Id BIGINT
)
Returns char(8)

[Code] .....

View 9 Replies View Related

How To Generate Sequence No?

Oct 29, 2007

Dear all,

I'm new in writing t-sql. Now I want to perform the following task:

1) pass a non zero integer to sql procedure, the procedure will select fields from tableA, for example,

if input parameter is 5, the result is (the column seq no is not in tableA, I want to generate sequence no):

seq no Customer code Customer name
------------------------------------------------------------------
6 abcde Peter
7 efghi John
8 jklmn Mary
:
etc


How to do this?

View 5 Replies View Related

Generate Sequence Number In Sql???

Mar 15, 2005

Hi,
I have written the following StoredProcedure





Code:

create Procedure spCreateQuestion(
@QuestionName varchar(30)
)

as

declare @newAnswerId int
declare @newQuestionId int

set @QuestionName='New Question'

BEGIN TRANSACTION Q1

--Creates New QuestionId with AnswerId 0
INSERT INTO Questions(QuestionId,Name,AnswerId)
SELECT 1 + COALESCE(MAX(QuestionId), 0),RTRIM(@QuestionName),0
FROM Questions

--QuestionId just now created
SELECT @newQuestionId=QuestionId FROM Questions WHERE Name=@QuestionName

BEGIN TRANSACTION QA1

--Create an AnswerId
INSERT INTO Answers(AnswerId)
SELECT 1 + COALESCE(MAX(AnswerId), 0)
FROM Answers

--AnswerId just now created(I hope not the best way to do like this)
SELECT @newAnswerId=MAX(AnswerId) from Answers --is it the best way to call statement like this or any other way better than this

--update Questions Table with this new Answerid
UPDATE Questions
set
AnswerId=@newAnswerId
where QuestionId=@newQuestionId

COMMIT TRANSACTION QA1
COMMIT TRANSACTION Q1



I think the second Transaction is not locking the table.so some how i should be
able to get the newly create AnswerId

i can't use the identity column in my tables

Can some one please have a look at it and suggest me how do we go about it..

View 8 Replies View Related

Generate Sequence No In One Column

Mar 20, 2007

helen writes "I have a sql table:
NAME AGE
Anna 10
Susan 5
Dina 12

Please help me to come up with a QUERY STATEMENT with this result:
SEQNO NAME AGE
1 Anna 10
2 Susan 5
3 Dina 12 "

View 2 Replies View Related

Generate Sequence Number Basing On Each Id

Mar 28, 2008

Hi all
anbody can help me writing sql code for this. All i need is to generate sequence basing on id_no
Ex: if ID=ABC(twice) in seq_col as abc --1
abc ---2
Tables which I have
Uniques_No ID_NO SEQ
---------------------------------------------
1 ABC
2 ABC
3 ABC
4 BBC
5 BBC


Expected results as below :
------------------

Uniques_No ID_NO SEQ
---------------------------------------------
1 ABC 1
2 ABC 2
3 ABC 3
4 BBC 1
5 BBC 2

Thanks in advance

View 4 Replies View Related

T-SQL (SS2K8) :: Programmatically Generate A Number Sequence?

Sep 3, 2014

I'm trying to do a simple insert into a table, something like this:

insert into sometable (ID, somecolumn)
select 'Task-ID', somevalue from SomeOtherTable
where something = 'someothervalue'
(or something to that effect)

So, the SELECT would generate something that looks like this:

ID somecolumn
-- ----------
Task-ID somevalue1
Task-ID somevalue2
Task-ID somevalue3
(etc.)

Here's where my problem comes in: ID is a PK, and needs to be unique. What I need it to do is this:

ID somecolumn
-- ----------
Task-ID.1 somevalue1
Task-ID.2 somevalue2
Task-ID.3 somevalue3
(etc.)

What I don't know is, how do I programatically generate the number sequence? Note: I do not have admin rights to the table, i.e. I cannot just change a column to IDENTITY.

Also the 'Task-ID' must remain part of the ID; in other words, I can't just generate a GUID, and it needs to be easily identifiable.

What I'm hoping to do is rewrite my SQL like this:

insert into sometable (ID, somecolumn)
select 'Task-ID.' + (generated seq #), somevalue from SomeOtherTable
where something = 'someothervalue'

Is there an easy way to do this?

View 9 Replies View Related

T-SQL (SS2K8) :: Insert Value Generate Special Character

Mar 12, 2014

I have a web page where the user can select the language (FR, EN, BG, ...) in a drop down list.

Next to the drop down list there is a text box where user can type the some text (translation).

User can add several description

On my web page, i have a button this button collect all information create an xml file and save all in database (sql server 2008)

that's work fine for some language => FR, EN and so on

But for bulgarian (bulgare) and greece there are some problem...

Some characters when i display it in sql look like => ???s??. ? d??ta?? a?t?

The value encoded by the user is => Ένωσης. Η διάταξη αυτή

but the result after t sql xpath is => ???s??. ? d??ta?? a?t?

Here is it my sql code where you can find my temporary table and my xml file and my xpath query

declare @tblTranslation table (idDocID int, languageID varchar(10), value varchar(500))
declare @Translations XML

set @Translations = '<?xml version="1.0" ?><Items><Item><eleKey>EN</eleKey><eleValue>This is a test</eleValue></Item><Item><eleKey>FR</eleKey><eleValue>test</eleValue></Item><Item><eleKey>BG</eleKey><eleValue>Ένωσης. Η διάταξη αυτή</eleValue></Item><Item><eleKey>HR</eleKey><eleValue></eleValue></Item><Item><eleKey>RO</eleKey><eleValue></eleValue></Item></Items>'

-- 2) fill the temporary table with information from the xml file

INSERT INTO @tblTranslation(idDocID, languageID, value)
SELECT
1
, Convert(nvarchar(max), i.query('eleKey/text()')) as colKey
, Convert(nvarchar(max), i.query('eleValue/text()')) as colValue
--
FROM @translations.nodes('/Items/Item') as x(i)
SELECT * FROM @tblTranslation

View 3 Replies View Related

T-SQL (SS2K8) :: Find String Before Character When Character Appears Multiple Times

May 17, 2015

I have a table that contains file paths as

ServernamefolderAfilenameA
ServernameFolderBFilenameB

and I need a query to return

ServernamefolderA
ServernameFolderB

I tried

SELECT DISTINCT left(Source, charindex('', Source)- 0) AS String
FROM Table

But that removes everything after the first and I need it to return all data before the last

View 5 Replies View Related

T-SQL (SS2K8) :: Replace Multiple Occurrences Of Same Character With Single Character

Aug 6, 2015

I have the following scenario, The contents of main file are like :

ServerCentral|||||forum|||||||||||||||is||||||the||best
so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it

And I need the output in the following form:

ServerCentral=forum=is=the=best
so=be=on=it

The logic being that multiple and consecutive occurrences of the special character, here - pipe , should be replaced by a single special character.

View 5 Replies View Related

Select Part Of Character String Based On A Character

Apr 15, 2004

I have data in a column that starts with 1-4 characters followed by a dash then followed by an number of characters (ex: EU-Surgery).

How do I select everything to the right of the dash when the number of characters to the left of the dash varies?

View 3 Replies View Related

Return Left-most Character From 8 Character String

Oct 1, 2014

I'd like to return the left-most character from an 8 character string & the third from the left character too.

Like this ABC00123 returns AC

$query = "SELECT LEFT(uninum,3), RIGHT(uninum,5), clmarea, Date FROM tblunimov";
$result = mysql_query($query) or die(mysql_error());

echo "<div class='tblstyle1'>";
echo "<table class='tblstyle1'>";
echo "<tr><th>ini</th><th>item</th><th>area</th><th>date</th></tr>";
while($row = mysql_fetch_array($result)){

[Code] ....

View 5 Replies View Related

Alphanumeric Primary Key

Jun 15, 2005

Is it possible to have an auto increment alphanumeric primary key eg A1, A2, A3

Thanks

Paul.

View 1 Replies View Related

Alphanumeric Id Column

Apr 9, 2008

I have set a column called "Anumber", I am
using a computed value to get the PK ID # and
a the letter "A" to it.

('A'+CONVERT([varchar](10),[requestid],(0)))

The above works fine unless I copy a record,
The copied record never displays the correct
value like "A55" it keeps "NULL" as it value.

Is their a better way to achieve a result as
described above ?

I am coming from Foxpro to SQL, Any advice would
be great.

Thanks

View 6 Replies View Related

Constraints With Alphanumeric

Apr 10, 2008

Hi experts,

I am executing a script to add a column to few tables and drop existing constraints, create new foreign key constraints, indexes etc. After we test this on our development database we have to deliver the script to the customer.
My concern here is, when I have to drop the constraint with the name, I am not able to do it because its been created with alphanumeric char in the constraint name. So I end up getting the constraint name for each table. And moreover,
I wont be able to deliver the same script to the customer as the alphanumeric in the constraint names on the customer's database will be different.

Any suggestions to overcome these alphanumeric in the constraint names?

Thanks much

View 20 Replies View Related

AlphaNumeric Function

Jan 10, 2008

Greetings all,

I have upsized 2 foxpro table to SQL Express.
In table 2 called Orphan I have/had a field that
would autonumber with the letter "U" first.
Example would create a AlphaNumber Like "U00001223"

In Foxpro this is a easy function, But I have been
unable to find a similiar way to do this in SQLEXPRESS.
Foxpro would have you define the field as Unique Number with
template defintion of "U"N8

In SQL I know you can use Numeric on a field for a unique number
but cant find setting to proced number with a "U"

Is this doable is in SQL and does anybody have a
reference point or example they could share ?

Any help would be great and hope my question made sense..

View 8 Replies View Related

WHERE Alphanumeric = Numeric

Dec 20, 2007



All,


I'm having trouble with a query where I need to limit a resultset by comparing (using a WHERE clause) a field that is alphanumeric with one that is numeric. I've tried converting, casting, and case statements, but I either get an overflow error or a big slap on the wrist by SQL Server 2005. Does anyone have any good solutions to this? I've been racking my brain for a while now.

Thanks for the help!
-Scott Mescall

View 3 Replies View Related

Alphanumeric Paging On GridView?

Feb 3, 2007

Hello,
I have a SQL database with about 300 company names and corresponding phone numbers.  I would like to show a list of linkbuttons titled A-Z and when pressed, rebind the sqldatasource so that my GridView will only show company names that start with that letter.
I know there are some examples on codeproject.com, but they are a bit over my head...  besides, I don't mind writing a custom select statement for the OnClick of every linkbutton if that's what I have to do.  Problem is I haven't a clue how to write a select statement that will return items who's first letter matches my desired letter?
 Any idea?
 Thanks,
-Derek
 

View 3 Replies View Related

Alphanumeric Autonumber Primary Key

Oct 26, 2004

Hi there,The age old question of creating a unique alphanumeric value automatically like ABC0001, ABC0002 Is it possible to do this automatically? That is, without having to update it which will slow the db down horribly?

View 4 Replies View Related

Auto Increment Alphanumeric ID

Nov 27, 2014

I'm using SQL 2008 with table [AgentDetails] and fields [IDCode],[FirstName],[LastName],..etc. [IDCode] is alphanumeric [AAA001].

Is it possible to increment both alpha & numeric when new record is inserted. e.g.

AAA001,AAA002......AAA999,AAB000,AAB001,...AAB999,AAC000,AAC001...etc. with a user function or some stored procedure.

View 2 Replies View Related

How To Find Alphanumeric Values In Name

Mar 26, 2015

I have a column called firstname ..in that it stores value like this

john smith
andrew jr
jim sr
andrew bar
tina *^
don $%

I need to retrieve all those rows where name consists of non alphabets...for example 5 and 6 has non alphabets..

I am using PATINDEX('%[^a-z]%',Firstname) function but if it finds space between names it is considering as error..I would like to find only non alphabets in name ..space is fine..is there any function to find out?

View 2 Replies View Related

How To Auto Increment An Alphanumeric Primary Key In SQL? :(

Aug 6, 2007

 How to auto increment an alphanumeric Primary Key in SQL? :( Because I want to add something like this in the Primary Key, for example i'll add a new data with an alphanumeric value of ABC-0001, then I'll add another 1, and it auto increments to ABC-0002 and so on.. How can I do it? And if I'll add a new alpha character in the Primary Key field, for example DEF-0001, then I'll add another and it auto increments to 002, and so on, and it will go back to 0001 if i'll use another combination of alpha characters. for example, i'll add a new alpha character AAA, will it go back to 0001 or it will continue? T___T I hope u get my point.. I want my table to look like this if i have added the dataABC-0001ABC-0002DEF-0001DEF-0002AAA-0001then if il add a new 1, for example ABCit will auto increment to 0003 in the same field, it will look like this after addingABC-0001ABC-0002ABC-0003DEF-0001DEF-0002AAA-0001Will it be possible? :( 

View 4 Replies View Related

DTS Excel Import AlphaNumeric As NULL

Feb 15, 2000

I have seen this problem posted several times and not seen an applicable solution yet. No offense, the suggestions may have been good for those scenarios, but not this one.

Several (at least 4) Excel Spreadsheets come from a vendor that I need to import to a table. They are uploaded via web to a directory so they can be imported by a scheduled DTS package.

Darren Green's site gave me some good info on how to get this far, btw.
But I cannot overcome the NULL-ifying of Column Headers for Numeric fields when the spreadsheet is imported.

I've tried:
DTSDestination("Yr1") = trim(DTSSource("F7"))
DTSDestination("Yr1") = CHR(34) & trim(DTSSource("F7")) & CHR(34)
DTSDestination("Yr1") = cstr("0" & trim(DTSSource("F7)))
DTSDestination("Yr1") = clng("0" & DTSSource("F7")))

Named ranges, along with any sort of spreadsheet editing is too time consuming.
And since they come from a vendor, having them come to us with the required items in place is not possible.

Even though it seems easy enough, it's a toughy.
Any help would be rewarded with respect and admiration.
Thanks, Mike

View 4 Replies View Related

Re:- Auto-incrementing An Alphanumeric Field

Jul 13, 2001

Hi

Everyone

I have a problem my table structure is like this

book_id varchar2(30)
book_name varchar2(30)


Now i want to enter the data into the fields like this

B001 Java Unleashed
B002 ASp Unleashed


and so on

So my Problemis how i will auto increment the book_id field though that is an alphanumeric field.

PLease help me that how i will insert and select from this table


Waiting for your help


Manish

View 1 Replies View Related

Sort Numerically From An Alphanumeric Column

Aug 9, 2006

Hi,

I need to get Sort numerically from an alphanumeric column

Regards,
Kihsore

View 3 Replies View Related

Query To Filter Alphanumeric Data?

Dec 4, 2011

I have alphanumeric data in a Table Assets. The column name is Milepost.

Column Values are like below

MilePost

1.24
1.61
4.56
4.78
5.45
6.91
7.19

[code]....

Now I want to select records between 1 to LR 4.41 which should return all records between 1 to LR 4.41

My below query is not returning proper values. So i need a correct sql query.

Select Milepost from Assets where Milepost between '1' and 'LR 4.41'

View 4 Replies View Related

T-SQL (SS2K8) :: Using Like To Identify Value That Contain Alphanumeric Values Other Than A-Z

May 11, 2015

Trying to use LIKE / NOT LIKE to identify values that contain any alphanumeric characters outside of A-Z e.g £%$^&*_-{[@ etc etc

The field should contain only values between A-G with a numberic e.g ABCD1234567... but some rows have characters such as above, some have spaces (weeps) , and some have letters outside the A-G range ....

View 7 Replies View Related

T-SQL (SS2K8) :: How To Convert Alphanumeric With Symbols To INT

Jun 5, 2015

I am new to SSC . I have a table that has NO.OF WORKING HOURS for 30 days. I am trying to convert all the values into INT as MINS to generate a SSRS report.

Note: There are some records with negative value (-4h15m)

Sample

NAME D1 D2 D3 ....D31
X1 9H 30M NULL 10M
X2 0M 1H 30M -4H45M
X3 20M -1H NULL

View 6 Replies View Related

Ignoring Numeric & Alphanumeric Values

Apr 2, 2008

Hi,
I am using SQL Server 2000. In database i am having one column named Address which contains full address of the customer. While searching i want to ignore starting numeric or alphanumeric values. Kinly guide how I can ignore numeric or alphanumeric values while searching the data.

View 8 Replies View Related

How To Add Comma To Alphanumeric Numbers In Text

Jul 18, 2013

I have a text where it is mix up of all alphanumeric values... For example like this

ACETAMINOPHEN 250 MG ASPIRIN65 MG CAFFEINE

ACER NEGUNDO POLLEN0.0021 G/ML ACER RUBRUM POLLEN0.0021 G/ML ACER SACCHARINUM POLLEN0.0021 G/ML ACER SACCHARUM POLLEN

So my requirement is i need to add comma (,) before every number value in this text.The text is different from each other.But main one what ever the number is there need to add comma before that one (decimal,numeric etc)

ACETAMINOPHEN,250 MG ASPIRIN,65 MG CAFFEINE

ACER NEGUNDO POLLEN,0.0021 G/ML ACER RUBRUM POLLEN,0.0021 G/ML ACER SACCHARINUM POLLEN,0.0021 G/ML ACER SACCHARUM POLLEN

View 6 Replies View Related

Non Significant '0' Removed From Alphanumeric Code

Feb 2, 2007

Hi all,

I have a problem with alphanumeric codes in SSIS.

I have a sql table with a varchar column which contains codes like '080101000', in my SSIS dataflow I have a lookup against this table and the column whith the code is used as output column for my lookup transformation.

In the advance editor the output column datatype is DT_WSTR, but when the code contains only numbers like the code '080101000' the first '0' is removed! It's like the code is at some point transformed to numeric and then inserted in the output column as a string. This in nonsense!!

Does anyone have an idea how to avoid this ?

View 9 Replies View Related

Transact SQL :: How To Find Alphanumeric Formats

Jun 12, 2015

create table example (f varchar(10))

insert into example values('fd')
insert into example values('fd')
insert into example values('fd1')
insert into example values('fd23')
insert into example values('fda23')
insert into example values('fd23g')

output:-

[A-Z][A-Z]
[A-Z][A-Z]
[A-Z][A-Z][0-9]
[A-Z][A-Z][0-9][0-9]
[A-Z][A-Z][A-Z][0-9][0-9]
[A-Z][A-Z][0-9][0-9][A-Z]

How to write SQl query to fetch this type of output.

View 6 Replies View Related

Read Chinese Character From SQL(SQL Server 2005) Database Table Column And Display Chinese Character

Feb 1, 2008

Hi!

I have a table like this below and it doesn't only contain English Names but it also contain Chinese Name.
CREATE TABLE Names
(FirstName NVARCHAR (50),
LastName NVARCHAR (50));
I tried to view the column using SQL Query Analyzer, It didn't display Chinese Character.
I know that SQL Server 2005 is using UCS-2 Encoding and Chinese Character uses Double Byte Character Set (DBCS) Encoding.
I want to read the FirstName and LastName columns and display in Window Form Data Grid and ASP.NET Grid View.
I tried to use this code below and it didn't work. It convert some of the English Name to Chinese Character and it display the chinese character and some still in the original unreadable characters.
Does anybody know how to read those character from SQL Table and display the correct Chinese Character without converting the English Name into Chinese also?
Thanks

int codePage = 950;
StringBuilder message = new StringBuilder();
Encoding targetEncoding = Encoding.GetEncoding(codePage);
byte[] encodedChars= targetEncoding.GetBytes(str);
.
message.AppendLine("Byte representation of '" + str + "' in Code Page '" + codePage + "':");
for (int i = 0; i < encodedChars.Length; i++)
{
message.Append("Byte " + i + ": " + encodedChars);
}

message.AppendLine(" RESULT : " + System.Text.Encoding.Unicode.GetString(encodedChars));
Console.Writeline(message.ToString());

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved