Create A Sql Loop Using Alphabet Characters
Jun 28, 2007
I have a basic while loop but I need to be able to increment the counter from one alpha character to the next:
declare @counter nvarchar(10)
set @counter = 'A'
while @counter < 'Z'
beginprint 'the counter is ' + @counter
set @counter = @counter + @counter
end
In this example I would need to see the following output:
the counter is A
the counter is B
the counter is C
the counter is D
.....
Is this possible in SQL?
View 2 Replies
ADVERTISEMENT
Aug 21, 2006
I need to create constraint in column to add only alphabet .
like "adc" ,"sdfsd" and not "1234adfd".plz reply soon.
View 4 Replies
View Related
Jul 9, 2007
Hi,Right, I have this problem, and it;s more through lack of understanding vb.net that well more then an actual problem I will out line what I want to do,basically it all revolves around a page that needs to be built when navigated to so it can be easily updated without anyone having to edit the code.Get all the table names from a databaseLoop through each of the results to build a statementNest a 2nd loop to split the returned data from the correct tableBuild a listbox for each table returnedThis is what I have currently, this works but the problem is, if another course is added, someone will need to manually edit the code on the page to add a new code to get the new course hence why I want to create a loop that gets all the data so all someone needs to do is put in the all table the new course name. Please noteI cut this down to just show 2 result but there is about 30 odd. 1 DBConn = New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=x;Password=xx")
2 'Dim DBDataAdapter As SqlDataAdapter
3 DBDataAdapter = New SqlDataAdapter("Select AOE,ADC, FROM TBL_Role WHERE Title = @ddlTitle", DBConn)
4 DBDataAdapter.SelectCommand.Parameters.Add("@ddlTitle", SqlDbType.NVarChar)
5 DBDataAdapter.SelectCommand.Parameters("@ddlTitle").Value = TitleDropDown.SelectedValue
6 DBDataAdapter.Fill(DSPageData, "Courses")
7
8 'Loop through each record, split at + and place in ListBoxs
9
10 VarDS = DSPageData.Tables("Courses").Rows(0).Item("AOE")
11 Dim VarArray As String() = VarDS.Split("+")
12 Dim i As Integer
13 For i = 0 To VarArray.Length - 1
14
15 Dim li As New ListItem()
16 li.Text = VarArray(i).ToString()
17 li.Value = VarArray(i).ToString()
18 Me.txtAOE.Items.Add(li)
19 Next i
20
21 VarDS = DSPageData.Tables("Courses").Rows(0).Item("ADC")
22 VarArray = Nothing
23 VarArray = VarDS.Split("+")
24 i = Nothing
25 For i = 0 To VarArray.Length - 1
26
27 Dim li As New ListItem()
28 li.Text = VarArray(i).ToString()
29 li.Value = VarArray(i).ToString()
30 Me.txtADC.Items.Add(li)
31 Next i
Now here is my pseudo code to what I roughly want to do, hope it makes sense to someone and someone can point me in the correct direction. Please note,I know the split bit works, so at the minute I am just trying to get the loop to get all my courses 1 DBConn = New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=wgw;PWD=wgwsql;")
2 DBSelect.Connection = DBConn
3 DBSelect.Connection.Open()
4 'Get the row number in the database
5 DBSelect.CommandText = "SELECT COUNT(*) FROM TBL_All"
6 DBResult = DBSelect.ExecuteScalar()
7 DBSelect.Connection.Close()
8 Dim Count = DBResult
9 'Get all the Tables and Keys in the Database's
10 DBDataAdapter = New SqlDataAdapter("SELECT * FROM TBL_All", DBConn)
11 DBDataAdapter.Fill(DSPageData, "All")
12 'declare all loop vars
13 Dim X As Integer
14 Dim Y As Integer
15 Dim i As Integer
16 'Loops through all the tables
17 Dim DSArray As String() = DSPageData.Tables("All").Items()
18 For Y = 0 To Count
19 Dim VarDS As String() = DSPageData.Tables("All").Rows(0).Item(DSArray(Y))
20 Dim SplitArray As String() = VarDS.Split("+")
21
22
23 For i = 0 To SplitArray.Length - 1
24 Dim Li As New ListItem()
25 Li.Text = SplitArray(i).ToString()
26 Li.Value = SplitArray(i).ToString()
27 Me.txt & DSArray(Y) &.Items.Add(Li)
28 Next i
29
30 Next Y
View 3 Replies
View Related
Oct 25, 2007
Hey
i have a table A that contains 3 columns : id, entry ,sessionid
i want to create a view on this table that will contain
- for each sessionid s in A --> select top 5 rows having s as sessionid and ordered by id desc
(s can have 1 or 2 or 5 or 300 entries i want to get only the latest 5 rows that correspond to this session)
I tried many queries and different combinations i could find one yet to do the following.
Can anyone help me plz?
Can we have a loop in a view?is it possible?
View 7 Replies
View Related
May 29, 2008
I have a stored proc I am running, and I would like to create a list of email addresses from a table and put that list into a variable. I did a basic while loop to work on syntax, but now I don't know how to actually get each address added on. Here's how I started it
declare @start int, @testEmail nvarchar(2000)
set @start = 1
set @testEmail = NULL
while @start <= (Select count(PADM_Email) from PADM_Emails)
Begin
--Print @start
set @testEmail = @testEmail + (Select distinct PADM_Email from PADM_Emails) + ';'
set @start = @start + 1
End
I know that the above is wrong, but I don't know how to get it right. Ideally, I want the @testEmail to look like this:
emailaddress1;emailaddress2;emailaddress3;
View 7 Replies
View Related
Jul 17, 2006
I need to run a script to create a trigger on 18 tables on 3 databases. The code is identical with the exception of the table and trigger names at the beginning. Does anyone know of a way to create them all with a loop instead of manually replacing the table and trigger names and executing over and over? I tried variables but get an 'Incorrect syntax near '@TriggerName' error.
if exists (select * from sysobjects where id =
object_id (N'dbo.tgUsersAudit') and
objectproperty (id, N'IsTrigger') = 1)
drop trigger dbo.tgUsersAudit
go
CREATE TRIGGER tgUsersAudit on tblUsers FOR insert, update, delete
AS
DECLARE @TableName varchar(128)
SET @TableName = tblUsers
..................from here the code is the same for all
View 11 Replies
View Related
Jun 19, 2008
Hi all
I'm new to sql and could do with some help resolving this issue.
My problem is as follows,
I have two tables a BomHeaders table and a BomComponents table which consists of all the components of the boms in the BomHeaders table.
The structure of BOMs means that BOMs reference BOMs within themselves and can potentially go down many levels:
In a simple form it would look like this:
LevelRef: BomA
1component A
1component B
1Bom D
1component C
What i would like to do is potentially create a temporary table which uses the BomReference as a parameter and will loop through the records and bring me back every component from every level
Which would in its simplest form look something like this
LevelRef: BomA
1......component A
1......component B
1......Bom D
2.........Component A
2.........Component C
2.........Bom C
3............Component F
3............Component Z
1......component C
I would like to report against this table on a regular basis for specific BomReferences and although I know some basic SQL this is a little more than at this point in time i'm capable of so any help or advice on the best method of tackling this problem would be greatly appreciated.
also i've created a bit of a diagram just in case my ideas weren't conveyed accurately.
Bill Shankley
View 4 Replies
View Related
Feb 11, 2015
For a new project. I need to create random alphanumeric characters as primary key values when inserting a record.
eg: cmSbXsFE3l8
It can start from 4 digit characters and can grow to 6, 7 as required
The database will involve more than 10000 concurrent users.
I don't want to use guid or auto increment integer or sequence.
View 9 Replies
View Related
Jun 15, 2015
I am developing for a customer and they want a search facility that uses boolean logic and special characters. So they want to be able to add "AND" "OR" "NOT" "*" and "?". And for this to effect the search in the predicted way and ranked. I was wondering if there is any examples of this type of search implemented?Â
View 3 Replies
View Related
Apr 24, 2007
What I'm trying to do is this;
I have a table with Year , Account and Amount as fields. I want to
SELECT Year, Account, sum(Amount) AS Amt
FROM GLTable
WHERE Year <= varYear
varYear being a variable which is each year from a query
SELECT Distinct Year FROM GLTable
My thought was that I would need to pass a variable into a select statement which then would be used as the source in my Data Flow Task.
What I have done is to defined two variables as follows
Name: varYear (this will hold the year)
Scope: Package
Data type: String
Name:vSQL (This will hold a SQL statement using the varYear)
Scope: Package
Data type: String
Value: "SELECT Year, Account, sum(Amount) AS Amount FROM GLTable WHERE Year <=" + @[User::varYear]
I've created a SQL Task as follows
Result set: Full Result Set
Connection Type: OLE DB
SQL Statement: SELECT DISTINCT Year FROM GLTable
Result Name: 0
Variable Name: User::varYear
Next I created a For Each Loop container with the following parameters
Enumerator: Foreach ADO Enumerator
ADO Object source Variable: User::varYear
Enumeration Mode: Rows in First Table
I then created a Data Flow Task in the Foreach Loop Container and as the source used OLE DB Source as follows
Data Access Mode: SQL Command from Variable
Variable Name: User::varYear
However this returns a couple of errors "Statement(s) could not be prepared."
and "Incorrect syntax near '='.".
I'm not sure what is wrong or if this is the right way to accomplish what I am trying to do. I got this from another thread "Passing Variables" started 15 Nov 2005.
Any help would be most appreciated.
Regards,
Bill
View 5 Replies
View Related
Aug 9, 2013
I need to increment values
I have written this script execute this :
This is for this scenario
Insert Into #String SELECT 'S601-S630',1,'Desc1'
Drop table #IDs
CREATE TABLE #IDs(ID INT IDENTITY(1,1) ,String VARCHAR(100),Flag Int,Description VARCHAR(1000))
CREATE TABLE #string(ID INT IDENTITY(1,1) ,String VARCHAR(100),Falg Int,Descript VARCHAR(1000))
DECLARE @min INT ,@Max INT ,@String VARCHAR(50),@Start VARCHAR(50),@End VARCHAR(50),@Flag INT,@Desc VARCHAR(1000)
[Code] ....
How I need to increment if the input is like this
Insert Into #String SELECT '6S01-6S30',1,'Desc1'
if alphabet is in middle of the number??
View 3 Replies
View Related
Aug 4, 2014
I have one table with one column:
region
Global
USA
England
Germany
Spain
What I want is that the first entry Global is always in the first row the rest should be ordered by alphabet.
I tried this but order by works only for the result of union:
SELECT * FROM csappcidb_region WHERE region='Global'
UNION ALL
SELECT * FROM csappcidb_region WHERE region<>'Global' ORDER BY region
What else can I do?
View 3 Replies
View Related
Jul 20, 2005
Got a tough one here for you SQL junkies.I'm working on a website (in ASP) for a national greek/collegeorganization. All it's college chapters have greek chapter names,i.e. Alpha Chapter, Delta Chapter, Delta Iota Chapter, Gamma PhiChapter, ect. ect.I need the SQL to return the chapters in greek alphabet ordering. Seebelow:1. Alpha2. Beta3. Gamma4. Delta5. Epsilon6. Zeta7. Eta8. Theta9. Iota10. Kappa11. Lambda12. Mu13. Nu14. Xi15. Omicron16. Pi17. Rho18. Sigma19. Tau20. Upsilon21. Phi22. Chi23. Psi24. OmegaBasically I need a way to specify this type of presedence for wordingand keep all the results in greek-letter order, so Sigma Upsilon comesbefore Sigma Tau, and so on.Also, to make it even more difficult, these are written words, not theactual greek symbols like α β etc. Everything is stored inthe DB as Alpha Beta Whateva Chapter.Hopefully some of you SQL junkies will be able to help on this one ;)
View 2 Replies
View Related
Feb 13, 2008
Hi all
Which function should I use to remove alphabet in a string?
For example, 60a , 50b, 34s, 34k. I want to remove the suffix alphabet. I tried to use filter but it return an array. i want the return value to be string or int to display.
Thanks
Bryan
View 4 Replies
View Related
Jun 4, 2014
I have a for each loop(ADO Enumerator) container which executes for each Advertiserid which is coming from database. In for each loop I have to create a new excel file with the advertiser name. So if the loop executes 7 times there should be seven excel spreadsheets with seven advertiser names.
How can i create an excel dynamically in the foreach loop container.
View 10 Replies
View Related
Oct 25, 2007
I am looking for the best way in SSIS to do the following. I have an SQL table that for each row in the table I want to take an element from the table do a lookup in a Teredata Table, return information from the teredata source. Use that returned data to do some calculations and create a derived column from my calculations and place the data into the same SQL table that I am parsing through.
Ideas?
View 1 Replies
View Related
May 20, 2014
There are about 300 fields in a views.
How to reorder all fields by alphabet?
For example, from script of views like below:
select name, date, amount from order
re-order fields' name like below:
select amount, date, name from order
For about 300 fields view, how to code to re-order fields' name?
View 6 Replies
View Related
Jul 23, 2005
Is there an easy way to loop through all rows and remove allinternational alphabet characters from a column in a table, for exampleremove German umlauts "ü" and convert them to a simple "u".Thanks,lq
View 12 Replies
View Related
Mar 27, 2007
Hi,
I have a report which lists out the employees' details. I need A-Z links above the report, On clicking on an alphabet, say "C", should display all the employee details whose name starts with the selected alphabet. In the stored procedure we can accept the character and return back those results. But it is not a drill report and we need the result in the same report. Is there any way so that on clicking each link, the output will be shown in the same report. Any help is appreciated.
Thanks in advance,
Sonu
View 4 Replies
View Related
Oct 14, 2015
I am trying to do the following;Â
declare @table table
 (
ID int,
InvoiceNumber varchar(10)
 )
insert into @table
values (1, 20),
(2, 20),
(2, 20),
(3, 60),
(3, 60)
I am trying to add sequential alphabet letters to the InvoiceNumber value on each row. Example;
1 20a
2 20b
3 20c
4 60a
5 60b
and so on.Â
View 4 Replies
View Related
Nov 25, 2013
create table #temp_Alpha_num (
[uniquenum] [int] Not NULL,
[Somenum] [int] Not NULL,
)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values
(1,121)
[Code] ....
For the somenum column I need to add alphabets to make them unique. for example
121a,121b,121c....121z,121aa,121ab,101a and so on...
View 4 Replies
View Related
Feb 19, 2008
In my application I must store over 16000 character in a sql table field . When I split into more than 1 field it gives "unclosed quotation mark" message.
How can I store over 16000 characters to sql table field (only one field) with language specific characters?
Thanks
View 3 Replies
View Related
Mar 5, 2008
Hi everybody,
I would like to know if there is any property in sql2000 database to separate lowercase characters from uppercase characters. I mean not to take the values €˜child€™ and €˜Child€™ as to be the same. We are transferring our ingres database into sqlserver. In ingres we have these values but we consider them as different values. Can we have it in sqlserver too?
Hellen
View 1 Replies
View Related
May 28, 2010
difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.
View 4 Replies
View Related
Feb 22, 2006
I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks
View 6 Replies
View Related
Jul 19, 2006
Good day experts,
I wonder if i got an answer for this.
How can i iliminate a letters from a set of integers and characters using a SQL Statement
for ex:
ABC9800468F
is that possible?
is there a function that i can use to iliminate them?
View 3 Replies
View Related
Mar 3, 2006
I have a foreach loop that is supposed to loop through a recordset, however it doesn't loop. It just repeats the same row, row after row.
I would like to look into the recordset variable but I can't because it is a COM object and the ADODB namespace is not available in the script task.
Any solution to this? anyone experienced anything similar
View 1 Replies
View Related
Jul 8, 2006
I have a table called Tbltimes in an access database that consists of the following fields:
empnum, empname, Tin, Tout, Thrs
what I would like to do is populate a grid view the a select statement that does the following.
display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I€™m trying to accomplish.
Tbltimes
|empnum | empname | Tin | Tout | Thrs |
| 1 | john | 2:00PM | 3:00PM |1hr |
| 1 | john | 2:00PM | 3:00PM | 1hr |
| 2 | joe | 1:00PM | 6:00PM | 5hr |
GridView1
| 1 | John | 2hrs |
| 2 | Joe | 5hrs |
im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.
View 18 Replies
View Related
Oct 22, 2015
I’m getting ASCII characters in one column of my table. So I want to replace same column value in NON ASCII characters.
Note – values in column must be same
View 10 Replies
View Related
Feb 23, 2006
I have source and destination table names in the database(one table) and I need to read the source and destination tables one by one...
My Lookp table is like the following...
Srn srctable desttable
1 SRC1 DEST1
2 SRC2 DEST2
3 SRC3 DEST3
Now I want one package to load from source to destination.. how do I do it.. I dont know how to use....
How do I run the pacakge for each of the rows... ..............................
View 1 Replies
View Related
Aug 4, 2004
Hi All,
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
Create PROC dbo.GrantPermission
@user1 varchar(50)
as
Grant create table to @user1
go
Grant create view to @user1
go
Grant create Procedure to @user1
Go
Thanks Guys.
View 14 Replies
View Related
May 20, 2008
the subject pretty much says it all, I want to be able to do the following in in VB.net code):
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
How would I do this?
View 3 Replies
View Related
Mar 3, 2014
I created a cursor that moves through a table to retrieve a user's name.When I open this cursor, I create a variable to store the fetched name to use within the BEGIN/END statements to create a login, user, and role.
I'm getting an 'incorrect syntax' error at the variable. For example ..
CREATE LOGIN @NAME WITH PASSWORD 'password'
I've done a bit of research online and found that you cannot use variables to create logins and the like. One person suggested a stored procedure or dynamic SQL, whereas another pointed out that you shouldn't use a stored procedure and dynamic SQL is best.
View 3 Replies
View Related