Help Manipulate Strings
Aug 1, 2007
Hey everyone,
So I'm querying from a database where one of the fields for each entry is a city. I would like to count the ones in each city. So I'm counting and grouping on city. Unfortunately, the data is not very consistent. Some are listed as Miami, FL and others simply as Miami. I don't need the state so I can just drop it, but I can't figure out how. I was using charindex and substring, but can't get it to work because if there is no ',' then the charindex is zero and substring doesn't work correctly. Any help would be greatly appreciated.
Thanks,
Keith
View 1 Replies
ADVERTISEMENT
Feb 19, 2007
I have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.
I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.
This is the basic logic goes soemthing like this:
select case new_accountant = 1 then 'acct/' end +
case new_advisor = 1 then 'adv/' end +
case new_attorney = 1 then 'atty/' end as String
from new_database
The output would be
Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/
So far, nothing I have tried has worked.
Any ideas?
View 2 Replies
View Related
Aug 4, 1999
Is there a way to manipulate a date variable into a specific month and day?? For example, I want whatever date the user will enter to be
changed to June 30 of the following year. So, if the variable @date is 12/12/1999, I want to change it to 6/30/2000. If the date
is 2/1/2001, I want to make it 6/30/2002
I can't use DateAdd because you need to know how many months to add or days to add. Basically, how do I retain the month and day while changing the year
the year based on a dynamic field. What I need is a DateChange function that is DateChange(mm, @month, @date) ? Is there
something like that?
Thanks,
Joyce
View 2 Replies
View Related
Jan 10, 2007
hi guys.
i want to know if is it possible to connect various databases with only one connection?
thx.
View 6 Replies
View Related
Apr 12, 2005
Hil,
I posted a thread here: http://forums.devshed.com/showthread.php?p=1061405 but was told it might be more useful for me to post something here... should have thought about it to begin with!
The picture is in that thread so do please look at it! http://forums.devshed.com/attachmen...tachmentid=7431 should be the URL.
As you see the top 3 records start and finish at the same time, as do the last 3 records. This can then also be repeated for the next day etc etc. What I want to happen is that the top 3 courses to appear on a single line and the 2nd set of 3 to appear on a single line also.
The main SQL to retrieve the records is as follows:
Code:
sqlqry = "SELECT * FROM t_sessions a INNER JOIN t_session_times b ON a.session_id = b.session_ID"
sqlqry = sqlqry & " WHERE right(left(convert(varchar,a.session_date,113),11),8)='"+displaym +"'"
sqlqry = sqlqry & " AND a.session_supplier_id=61"
sqlqry = sqlqry & " AND a.session_status ='A'"
sqlqry = sqlqry & " ORDER BY a.session_date, b.session_start, b.session_end"
then while this recordset hasn't come to an eof(end of file) I execute:
Code:
query = "SELECT course_name,outline_name"
query = query & " FROM t_course_ref"
query = query & " LEFT JOIN t_it_outlines ON t_course_ref.course_id=t_it_outlines.course_id"
query = query & " WHERE t_course_ref.course_id="+cstr(rs.fields(1).value)
Thanks!
View 5 Replies
View Related
Nov 14, 2006
I remember I was able to view, add, alter table on the mdf in either sql express 2005 or in vb.net 2005. But right now I can't do either, what do I have to to change
View 1 Replies
View Related
Jun 7, 2007
Hi,
i want to programmatically manipulate the property 'Name' of a ControlParameter inside a InsertParameters tag.
This the aspx code: ------------------ <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<% ......... %>" InsertCommand="INSERT INTO .......)" <InsertParameters> <asp:ControlParameter Name="myname" ControlID="na" PropertyName="text" /> </InsertParameters> </asp:SqlDataSource>
code-behind: ------------
Dim a As String a = SqlDataSource1.InsertParameters.Item(0).ToString
but 'm stuck here
Thanks for help
Tartuffe
View 1 Replies
View Related
Apr 21, 2008
hi,I have a page Price List with GridView to display only two columns of a table (Product name & price). Page size=20. I use SqlDataSource for it. <asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet"In my case the SqlDataSource control return data as a DataSet object. So the DataSet object contains all the data in server memory. I need to create Print Preview page with other columns (Product name & price & Vendor and Date Issue) without paging.I'm going to save dataSet in Session and in Print Preview page use it as datasource (without having to run another query).But I have no idea how to save this DataSet in Session. I don't know the DataSet Name. Any ideas? Thanks.
View 2 Replies
View Related
Oct 15, 2006
what re the sql functions to manipulate numbers, decimals, dates..
1/ like if I have 123443.78654 I want to display just 2 decimals : 123443.78 or 3 decimals ..
2/ also if I want to have bankers rounding
3/ how about dates conversion : from string to date type and vice versa and adding dates, substracting dates, getting the day, the month....
what re the SQL functions to do that pls
View 4 Replies
View Related
Dec 5, 2007
Good morning, everyone. Maybe I'm just having a brain fart, but I'm totally new to SSIS (I dabbled very little with DTS in the 2000 days) and cannot for the love of me figure out how to achieve my goal with it:
My company needs to extract data from a variety of sources; tab-delim files, Access databases, other SQL tables and the like. I know how to do this. However, I need to perform data manipulation queries on this data before I place them into SQL tables, as I want to avoid having umpteen temporary tables that I'll need to add checking for. My predecessor did everything in Access, and has a 76-step process (yikes!) that basically will grab all the data, do some minor manipulation, and plop it into a temp table (this is still in Access, not SQL), then repeat the same thing dozens of times.
To give you an example, here's a sample of what I want to do:
- Extract several columns of data from a tab-delimited file on the local drive. This I know how to do already.
- Perform some data cleanup and manipulation functions on this data (specifically, obtain the lowest value out of three columns, with the added caveat that I make sure it's not zero to begin with). I have the SQL code for this already written.
- Store the results of this data somewhere, so I can pull it and apply additional logic to it; for example, take the lowest value I've retrieved, and update the corresponding column in another database table with it.
Basically, is there any way to avoid the use of dozens of temp tables? There's a lot of data which needs to be pulled in, manipulated, and spit back out to be manipulated by something else a little later on, and the way my predecessor did it was, as I said, to use dozens of Access "Make Table" queries for every minor thing. It's not a big deal if I need to do it, just I'm trying to consolidate the steps needed, as the old way is very inefficient. I've been at this job a month and I'm still trying to wade through all of his queries to discover just what they do, and look into combining several of them.
Forgive the slightly newbish question, but as I stated I've not worked with SSIS really. I'm in the process of learning it better, as I'm sure it can fit our needs.
View 5 Replies
View Related
Dec 22, 2006
I'm very new to SQL server and can use some help. MyTable has ColumnA, which contains strings composed of 1 to 4 numeric characters (0 thru 9) followed by alphabetic characters. For example, "53ASDF". In my query, I need to create ColumnB, which takes the numeric prefix from ColumnA's string and prepends it with zeros, if necessary, to create a string of exactly 4 numeric characters. For example, I could get the following result:
ColA ColB
"6abc" "0006"
"457def" "0457"
"7232hij" "7232"
I have implemented a temporary solution using a CASE statement:
SELECT ColA, ColB =
CASE
WHEN ISNUMERIC(LEFT(ColA, 4)) = 1 THEN (LEFT(ColA, 4))
WHEN ISNUMERIC(LEFT(ColA, 3)) = 1 THEN '0' + (LEFT(ColA, 3))
WHEN ISNUMERIC(LEFT(ColA, 2)) = 1 THEN '00' + (LEFT(ColA, 2))
WHEN ISNUMERIC(LEFT(ColA, 1)) = 1 THEN '000' + (LEFT(ColA, 1))
ELSE ''
END
FROM MyTable
Because of additional complexities, I need to implement the solution with a loop instead of a CASE statement. Can someone please describe such a solution?
I'm very confused about how variables work in SQL Server, but made an attempt to implement a solution. Hopefully, someone can make corrections and describe how to use it with a SELECT statement. I would greatly appreciate any suggestions. This is what I started with:
DECLARE @ColBstring char(4)
DECLARE @num int
SET @ColBstring = ''
SET num = 1;
-- Get the numeric prefix from ColumnA's string
WHILE(isnumeric(substring(colA, 1, num)) = 1)
@ColBstring = (substring(colA, 1, num)
num = num + 1
-- Prepend the ColumnB string with zeros
WHILE(LEN(@ColBstring) < 4)
@ColBstring = '0' + @ColBstring
Thanks for any help,
Mike
View 1 Replies
View Related
Feb 7, 2007
After generating one of my reports, I process the XML output through an XSLT stylesheet and export it to a text file. The issue is that after the export, the generated output text file begins with the special Byte-Order-Mark marker "EF BB BF" standard to Unicode files encoded in UTF-8, UTF-16 or UTF-32. I have explicitly set the attributes of the xsl output element to <xsl:output encoding="us-ascii" media-type="text/plain" method="text">, but it seems as though those are ignored when the output file is written. I cannot have these characters, because I am generating a fixed-width file for input into a legacy system.
Any suggestions or thoughts on what is causing the BOM to be written to my file, even though I have set the encoding to be different than UTF-8?
View 1 Replies
View Related
May 7, 2007
Hi,
I am using OLE DB provider for Foxpro (VFPOLEDB.1) to query DBF files. I need to migrate the content of these files to a SQL Server 2005 database.
These DBF files have some (actually a lot) records marked as deleted using the DBF 'deleted' flag. When I submit a SELECT command to the OLE DB Provider, it returns me all the non-deleted records from the file.
It is very Ok as long as the 'deleted' rows actually have no more business value, but in my case, I need to do some processing on them, and even to migrate their data.
What are the options available for me to be able to query and differentiate the 'deleted' records ?
Thank you in advance,
Bertrand Larsy
View 6 Replies
View Related
Mar 4, 2008
I have somw tables like Product, Sales, Customer.
I used UDTs like ProductCode = nvarchar(30)
CustomerCode= nvarchar(15)
How can I modify my UDTs ?Is there any quick way for that ?
****
Thanks.
View 3 Replies
View Related
Jun 4, 2015
I have one column CandidatePlaced (Data type Boolean).
Using data conversion i changed data type to DT_WSTR and then i used derived column to manipulate the data. Ex. 1 = "Yes" and 0 = "No"
[Candidate Placed ?] == "1" ? "Yes" : "No"
But at end of result i got all the columns as No. Some should be Yes.
View 7 Replies
View Related
Jul 3, 2007
I have a SQL2000 DTS package that executes vbscript to loop through a recordset which:
- runs a stored procedure and populated tables
- builds a recordset from the populated tables to write records to an Excel file
- writes status to text files with either the error or success notices
I use FSO to set up the success and error files, but the scheduled job in SQL2005 which calls the SSIS package returns the following error:
"Retrieving the file name for a component failed with error code 0x0015F74C"
I can successullly run this (vbscript) in both the SSIS package via the BI Development Studio and in MS Access (exactly the same code in both) - but not as a SSIS package called in a scheduled job in SQL2005.
I am at an impasse with this ... any and ALL assistance would be GREATLY appreciated.
TIA,
Bob
View 1 Replies
View Related
Sep 23, 2013
I am trying to manipulate the data being imported from another data source. See below:
I need to make an IF THEN statement: If KeyDate =< 01/01/2013 THEN STATUS = 'Disposed'
Notice I am adding data in the last column as everything is 'Active'
SELECT dbo.UserConfig.Id,
dbo.UserConfig.ServerConfigId, dbo.UserConfig.DisplayName,
dbo.UserConfig.UserName, dbo.UserConfig.MailboxSMTPAddr,
dbo.UserConfig.OverrideSMTPAddr,
[Code] ....
View 3 Replies
View Related
Aug 22, 2002
I have a table that looks like the example below. I need to return the tindex and the entire description on one row. Any clues? I'm drawing a blank.
thanks for you help
tindex tdline description
1234 1 Talk to Mr. Cartwright about
1234 2 new issues with patent law. Conferece
1234 3 call to discuss payment of past bills.
I need to see
1234,Talk to Mr. Cartwright about new issues with patent law. Conferece call to discuss payment of past bills.
View 4 Replies
View Related
Jul 18, 2007
Hi everyone.
Is it possible to put in a string value as one of the results? I'm trying to produce a string in the data table is the value is null so I want to do something like:
iif(somevalue is nothing, 'Other', somevalue)
Thank you in advance.
View 1 Replies
View Related
Nov 11, 2006
I am trying to develop a web site. I have a local ms sql database on my machine.
I am trying to connect to a ms Sql database on a goDaddy server from the application.
I am trying to understand the connection string and its total properties.
here is what I think should be in my web.config file
<
add name="Personal" connectionString="Server=whsql-v12.prod.mesa1.secureserver.net;
Database=DB_XX10;
User ID=myID;
Password=myypassword;
Trusted_Connection=False" providerName="System.Data.SqlClient"
/>
<remove name="LocalSqlServer"/>
can someone please tell me where I am going wrong, Thanks for your help.....
View 7 Replies
View Related
Feb 7, 2007
I am using query strings to pass data from web form to web form and I have two questions. First if i use a asp:sqldatasouce to fill up a grid view and I have my select command set to a paramater that get whatever is in the query string it will not work because whatever is in the quers string gets a " ' " put in front and in the back of it. So if the query string was 5 whene it does the sql statement it sets my paramater = '5' not just 5 so its wont work. How can I fix this using the asp:sql datasource my aspx code looks like
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Rental PropertiesConnectionString %>"
SelectCommand="SELECT * FROM [APARTMENTS] WHERE ([PROPERITY_ID] = @PROPERITY_ID)">
<SelectParameters>
<asp:QueryStringParameter Name="PROPERITY_ID" QueryStringField="key" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
Also since i have not been able to get around this so i have been wrting code in vb.net to attact a dataset to a grid view to populate it based on the query string i would do the following in vb.net to get ride of the ' in front and behind the query string
Dim y as string = "'" // " ' "
key = Request.QueryString("key").trim(y.tochararray)
But now i am doing another project in C# and I have re-written the above code in C# it will run but it will not take the " ' " out form infront or behind key. How does this need to be changed up?
string y = "'";
key = Request.QueryString["key"].trim(y.tochararray());
View 3 Replies
View Related
Aug 28, 2007
How can i make a sum (concatenation) of strings of one column in a table.
for example i have a table like this
field1 field2
1 abc
1 bcd
2 sdf
2 sdd
I want to get these strings added group by field 1
Thanks
View 5 Replies
View Related
Nov 23, 2007
I am submitting a telephone number into a table. I have 3 boxes for the telephone number. Telephone1,Telephone2,Telephone3. I need to insert the values of the 3 text boxes into a column called phone in my table.
so like
InsertCommand="INSERT INTO customer_mod (phone) Values (@Telephone_1)
<asp:formparameter name="Telephone_1" formfield="Telephone1+Telephone2+Telephone3" />
I don't think that is gonna work, so can you please help me make that code work?
View 3 Replies
View Related
Nov 24, 2007
I developed an application and deployed it onto a hosted provider. I use SQL 2005 Express for development SQL 2000 for production. Both systems work.
I backed up my production database and wish to use it for my development testing. I restored it as a SQL 2000 database on my development machine and changed my connection strings to point to it. I added it as a new Data Connection in Database Explorer and can see all of the data through it, but I see that all the tables now have the owner name in parenthesis as part of the table name.
My application now fails trying to find any of the tables. It works if I include the owner name as part of the SELECT statement. So much for data separation.
I don't know where to start. Is this an issue between SQL 2005 Express and SQL 2000, or is it an issue between SQL and ASP.NET, or is it a hosting provider issue, or what?????
View 6 Replies
View Related
Jan 24, 2008
Hey guys, don't know if this is in the right place but here goes.
I'd like some advice on optimizing the way I write my code. Here's the scenario. I have a select string for 3 different tables. One for the main gridview, and two others that are binded to dropdownlists.
So I created a Sqldatasource1,2,3 and had to create the connection strings for each one.
ex. SqlDataSource1.ConnectionString = "blah", SqlDataSource2.ConnectionString = "blah", SqlDataSource3.ConnectionString = "blah"the thing is, the connection string itself is the same. Is there a way to consolidate that? So that when I use a select/update/insert command for each sqldatasource i dont have to have another connection string? Also, having multiple connectionstrings, does that slow down the performance?
View 3 Replies
View Related
Feb 28, 2008
In an ASP.NET app, where is the preferred place (or your preferred place, I should say) to store any SQL Statements (updates, selects, stored procedure calls, etc.) you have? I have been using a key/value pair in AppSettings in my web.config but just feel like that isn't the best way. What say you?
View 4 Replies
View Related
Mar 14, 2008
Hi,
Im struggling to work out the connection string to my ms sql database.
My website is being hosted with freezone.co.uk and I have a ms sql database called "aspnetdb". I usually log into the database with a username but no password. I don't no what other information is needed to create my connection string?
Thanks, Mark Lawton
View 2 Replies
View Related
Apr 4, 2006
I've got a GridView, a datasource, 2 textboxes and a button. If I choose to bind the GridView to the datasource the correct data is shown. But if I modify the datasource and chooses the TextBoxes as Parameter Controls it doesn't work out as I expected.
Can I please have a step-by-step response which will work? And expecially if one if the TextBoxes eguals nothing/null. Because this is very important, too, and that part doesn't work either.
View 1 Replies
View Related
Jan 9, 2001
This seems so simple but I can't find any examples to do it..
I have the following in a text file "Lastname, Firstname MI" and I want to select each part separetly.. What would be the SELECT statement to get the Lastname up to the comma, then the Firstname after the comma, then the MI after the last space..
Thanks for the help, and any reference to learn more.
View 2 Replies
View Related
Feb 27, 2001
I have a varchar field that contains answers to questions separated by commas. Say there are 4 questions for each user. Here is an example of what the table would look like:
User Answer
1 Good,Fair,Good,Bad
2 Bad,Good,Good,Good
3 Fair,Good,Bad,Fair
I need to write a stored procedure to report off of that separates the Answer field into 4 different columns. How can this be achieved? Any assistance would be greatly appreciated.
View 1 Replies
View Related
Oct 11, 2006
In MySQL I can represent a string as the following:
X'6162636465'
However, I can't seem to find an equivalent that is supported MS SQL from the last hour and a half of searching the net. Does anyone know of any way to do this?
View 2 Replies
View Related
May 13, 2004
I have a column called professional_degrees in my table
and it has the following data
professional_degrees
bs,ms,mba
bs,mba,
bs,ms,bs,ms
mba,mba
bs,mba
In the above data u can see some of the degrees are repeated.So how can i find them and delete them from the table.
Thanks.
View 11 Replies
View Related
Apr 1, 2008
I have a small problem that I can't figure out. Here's my table:
----------------------------------------
Members
----------------------------------------
ID | Name | Image
----------------------------------------
When a user signs up to become a member, they can upload an image, which will be stored under Image. It will also be stored in a folder. For example, if the user with the ID of 14 uploads an image, it will be stored in 'livefiles/Members/14/image.jpg'. If the user does not upload an image, NULL is stored under Image, and the user has a default image, which is stored under 'livefiles/MembersDefault/1/image.jpg'.
What I want to do is return the path of where the image is in the folders. Basically...
If Image is not null
SELECT 'livefiles/Members/' + ID + '/' + Image AS ImagePath
If Image is null
SELECT 'livefiles/MembersDefault/1/image.jpg'
I'd rather not do this with an IF statement, I'm doing a lot of other SELECTS, and I don't want to have to copy all that code. Here's what I tried...
COALESCE (NULLIF('livefiles/Members/' + CONVERT(varchar(20), ID) + '/' + Image, 'livefiles/Members/' + ID + '/'), 'livefiles/MembersDefault/1/image.jpg') AS ImagePath
but that gave me an error saying:
Conversion failed when converting the varchar value 'livefiles/Members/' to data type int.
View 4 Replies
View Related
