Replacements Of Substrings In Strings

Apr 19, 2006

This is not a piece of cake as I thought.

Have to replace few characters with few other characters in the string.

Now, I am using nested Replace:

(Replace(Replace(MyString,'Ü','UE'),'Ö','OE')

This example is simplified, number of replacements is more than eight of them.

Is there any other, more elegant, way to do this in just one command?

View 2 Replies


ADVERTISEMENT

Replacements Of Substrings In Strings

Apr 19, 2006

This is not a piece of cake as I thought.

Have to replace few characters with few other characters in the string.

Now, I am using nested Replace:

(Replace(Replace(MyString,'Ü','UE'),'Ö','OE')

This example is simplified, number of replacements is more than eight of them.

Is there any other, more elegant, way to do this in just one command?

View 2 Replies View Related

How To Do Text Replacements

Sep 15, 2005

I am trying to do a text replacement to reflect changes where I'vestored data.A field, backup_archive_filename, contains the url path. I've sincechanged the directory structure and wish to change whats stored in thetable.Example:\10.0.12.110SQLSafeCOGNOS-DEV2005-08-29_2017m_51s_Diff_COGNOS-DEV_cm.safe\10.0.12.110SQLSafeTLS-D-AN0012005-08-29_2041m_11s_Diff_TLS-D-AN001_Northwind.safeI want to change SQLSafe to SQLSafeDiff or SQLSafeFull depending whenthere is either %Diff% or %Full% in the string to reflect the change inthe directory.I wanted to do something like:update backups_setsSET backup_archive_filename = <<get first part>>+ 'SQLsafeDiff' +<<getlast part>> where backup_archive_filename like '%_Diff_%'I need a function for <<get first part>> like EXTRACTSTR(backup_archive_filename, '',3) would return '\10.0.12.110SQLSafe'. Icant find a built in function that can pick apart fields based on aseperator.TIARob

View 1 Replies View Related

'group By' And 'substrings' Query Help

Jan 5, 2007

i have a table like this:


Code:

Product
id - varchar(10)
description - text



the ids are something like this
mod01-01
mod02-01
mod03-01
mod01-02
mod02-02
mod03-02
mod03-03
mod03-04
mod04-01
....

now, i am trying to group by and count all of the ids that end with '01',

i was thinking of something like
select count(id)
group by id
where id= ???

and the second query i need to make is one that counts not one but all the ids that end with the same last 2 digits but in one single query, how can i do that? as a possible hint, is that the only thing i KNOW that the last 2 digits can only be 01,02,03,04 and 05 but still im a bit stuck in here the result should look something like this

id total
01 4
02 3
03 1
04 1

or what suggestions can you give me for this query? even if you just help me with the first query, that would be great, thanks!!
thanks!!!

View 6 Replies View Related

Anyone Have A Snazzy Way To Count Substrings?

May 3, 2004

Hi All,

I have a varchar that contains a comma-delimited list of integers, such as "12,34,56,78,123,1,123455".

I need a way to count the number of numbers in the string (or, perhaps, better stated as "I need a count of substrings") ;)

I'm thinking there must be a number of ways to calculate the number of numbers in my list of numbers, but I can only seem to come up with looping through the string/varchar and counting the number of commas, and then adding one to that final count.

Anybody know of a "cooler" way to do this? Everything I can think of involves stepping through a character at a time...

any (printable) thoughts?
THanks!

View 14 Replies View Related

Taking A Filename Into Substrings

Apr 11, 2008

I have filename with the following format

461#Pipeline#0978541235#1.pdf

where # is the seperator between different data in the filename, in .NET i achieved to save the different data in the filename to seperate variables like this


Dim siebelId, accountNumber, docType As String

Dim counter As Integer = 0

Dim sites As String() = Nothing

sites = extension2.Split("#")

Dim s As String

For Each s In sites

If counter = 0 Then

siebelId = s

End If

If counter = 1 Then

accountNumber = s

End If

If counter = 2 Then

docType = s

End If

counter = counter + 1

Next s


How can i achieve this in SSIS where i can save these to variables for later use in an Execute SQL Task ?

View 9 Replies View Related

How To Perform An Update To A Collumn With Two Substrings

Mar 22, 2005

I want to do the following in an update:

Update [table]
Set collumnName = Substring(collumnName, 1, 2) = '11' AND Substring(collumnName, 3, 5) = '00000'
Where (conditional statement)

SQL is not letting me perform the update because of the AND statement between the two Substring's. Is there anyway around this...is there anything else I can do to acomplish what I'm trying to do.

Thanks in advance everyone.

RB

View 5 Replies View Related

Transact SQL :: Search Substrings In Column Values

Jun 19, 2015

I have 2 tables in a SQL Server database:

Table A(Value), which contains some strings in the column "Value"

Table B(Key,Text), which also contains strings (in the column "Text")

Now I want to find all rows in B which contain at least one string of A and create a result table X with all found rows in B. B should contain the found keys and all found substrings for this key (separated with a comma)

The solution I am looking for may not use a Cursor and may not use the CONTAIN-Statement (fulltextsearch feature)..

View 5 Replies View Related

Concatenate Strings After Assigning Text In Place Of Bit Strings

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

? On How Add Strings Together

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

Can I Use Strings In Iif?

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

Connection Strings

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

Help With Query Strings

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

Addition Of Strings

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

InsertCommand, Add Strings...

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

Connection Strings

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

Connection Strings

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

Where Do You Store Your SQL Strings

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

Connection Strings

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

Multiple Strings In An SQL

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

Select Strings...

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

Parsing Strings

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

MS SQL Representing Strings Has Hex

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

Duplicate Strings

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

Select Different Strings

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

Splitting Strings

Nov 24, 2006

I everyone, I have been on the admin side of IT for the past 20 years and recently started to do some scripting (VBscript) and a little SQL.

I have developed a solution to meet the needs of some federal auditors, but not really met my needs yet.
What I have done is this.

I use MS Logparser to go out to 64 servers and copy the event logs into a DB on a SQL 2000 Ent. Server.

On the SQL server I have one StoredProcedure that parses out information from the security event log DB and put that info into a temp DB.


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[stp_SecurityAuditReport]

AS

TRUNCATE TABLE SecurityEvents_Tmp

-- Parse Bank Number & UserName
INSERT INTO SecurityEvents_Tmp(DepartmentNumber, UserName, EventLog, RecordNumber, TimeGenerated, TimeWritten, EventID, EventType, EventTypeName, EventCategory, EventCategoryName, SourceName, Strings, ComputerName, SID, Message, Data)
SELECT
DepartmentNumber = '001',
UserName = CASE
WHEN Strings LIKE '[0-9][0-9][0-9]%'
THEN SUBSTRING(Strings,1,charindex('|',Strings,1)-1)
WHEN Strings LIKE '-|[0-9][0-9][0-9]%'
THEN SUBSTRING(Strings,3,charindex('|',Strings,3)-3)
WHEN Strings LIKE '-|[a-z]%'
THEN SUBSTRING(Strings,3,charindex('|',Strings,3)-3)
WHEN Strings LIKE 'Account Unlocked. |%'
THEN SUBSTRING(Strings,21,charindex('|',Strings,21)-21)
ELSE SUBSTRING(Strings,1,charindex('|',Strings,1)-1)
END,
Events.*
FROM Events
JOIN EventsToLog on Events.EventID = EventsToLog.EventID
WHERE SID NOT LIKE 'S-%'


-- Update blank usernames
UPDATE SecurityEvents_Tmp SET UserName = 'NO USERNAME'
WHERE UserName = '' OR UserName = '-'

-- Update DepartmentNumbers with zeros
UPDATE SecurityEvents_Tmp SET
DepartmentNumber = CASE
WHEN UserName LIKE '[0-9][0-9][0-9][a-z]%' OR
UserName LIKE '[0-9][0-9][0-9]#%' OR
UserName LIKE '[0-9][0-9][0-9]$%'
THEN SUBSTRING(UserName,1,3)
ELSE '001' END


As you can see, we use 3 didgit numeric prefixes on all Departmental employee accounts. This is later used to produce departmenntal user audit reports.

I then have this script in a DTS that exports the report to an excel spreadsheet. (All works well for this purpose!)


DECLARE @TimeGenerated datetime
SELECT @TimeGenerated = TimeGenerated FROM SecurityEvents_TimeGenerated

DECLARE @TimeGeneratedEnd datetime
SELECT @TimeGeneratedEnd = TimeGeneratedEnd FROM SecurityEvents_TimeGenerated

SELECT DepartmentName = CASE WHEN b.DepartmentName IS NULL
THEN 'All Department' ELSE b.DepartmentName END,
a.EventID,d.EventDescription,a.UserName, a.TimeGenerated,c.Email1,c.Email2,c.Email3,c.Email4
FROM SecurityEvents_Tmp a
LEFT JOIN DepartmentList b on a.DepartmentNumber = b.DepartmentNumber
LEFT JOIN EmailToList c on b.DepartmentNumber = c.DepartmentNumber
JOIN EventsToLog d on a.EventID = d.EventID
WHERE b.Departmentnumber in (select Departmentnumber from Departmentlist)
AND a.TimeGenerated BETWEEN @TimeGenerated AND @TimeGeneratedEnd
ORDER BY b.DepartmentNumber,a.EventID,a.TimeGenerated


This combination of utils and scripts does very good for producing generic security reports for branch officers.

But now I am getting requests to justify/explain what is in these reports. The problem I have is that the information needed to delve further into the event logs is in a field called Strings. This field not only changes in length and the amount of fields within this string, but the information in this field changes depending on the type of event record it came from.


This is the Strings field from a failed logon (529)
200jenil|DOMAIN|10|User32 |Negotiate|SERVER|SERVER$|DOMAIN|(0x0,0x3E7)|6920|-|10.190.12.10|48397

And this is from Event ID 642 which was an account being created.
-|381$cmiller|DOMAIN|%{S-1-5-21-3554868564-134719009-1577582102-7972}|Jmotta|DOMAIN|(0x0,0x58F635E)|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|%%1792|-|-

Now, my script does a good job of getting the first user name out but as in the 642 event the second users name would be useful as well. This is the person that created/modified the user account.

So what I was hoping was that I could use a function (or whatever) to automaticaly split the Strings value into it's individual components and put them into an auto-sizing temp table as something like Field1, Field2, Field3, and so on until the end of string.

I could then use a case to get the information needed.

HELP PLEASE!!



Thank You,

John Fuhrman
http://www.titangs.com

View 15 Replies View Related

SP Strings Parsing

Dec 11, 2006

Hello everyone, I have this SP and can't get it to work on my SQL2000 server. I just can't seem to figure out what syntax error I am making.
(This works on my test SQL2005 server)


Truncate Table [SecurityEvents_Temp]

Insert Into SecurityEvents_Temp (
[BankNumber],
[UserName],
[DomainName],
[EventLog],
[RecordNumber],
[TimeGenerated],
[TimeWritten],
[EventID],
[EventType],
[EventTypeName],
[EventCategory],
[EventCategoryName],
[SourceName],
[Strings],
[ComputerName],
[SID],
[Message],
[Data]
)
Select
[BankNumber] = '001',

[UserName] = (Select [Value] From [dbo].[fn_Split]([Strings],'|') where idx = 3)

[DomainName] = (Select [Value] From [dbo].[fn_Split]([Strings],'|') where idx = 4),

[SecurityEvents].*
FROM [SecurityEvents]
JOIN [EventsToLog] on [SecurityEvents].[EventID] = [EventsToLog].[EventID]
WHERE [SID] NOT LIKE 'S-%'


Query Output:
quote:
Server: Msg 170, Level 15, State 1, Line 28
Line 28: Incorrect syntax near 'Strings'.
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'Strings'.



Thank You,

John Fuhrman
http://www.titangs.com

View 16 Replies View Related

Need HELP Converting Strings

Aug 23, 2007

I need to convert string data so that I can use it in a view.

example
xxx-xx-xx
xxx-xx-xx
xxxxxxx
xxxxxxx
xxxxxxx
xxx
xx
I need to be able to add the dashes in for the data that has 7 characters and disregard the data that is less then 7 characters

Jeff

View 8 Replies View Related

Concatenation Of Strings

Mar 10, 2008

Guys,

I have following table where I need to concatenate varchar column.

For example

IDCOMMENT
__________________
1JOHN SMITH
1 SURRENDER
1TO COPS

I want to be able to group by ID and concatenate COMMENT field to 'JOHN SMITH SURRENDER TO COPS' for ID 1

Is there any way to accomplish this?

Any suggestions and inputs would help

Thanks

View 5 Replies View Related

Concatenating Strings

Jan 27, 2008

I find myself between rock and hard place.

My requirement is to simply output a flat file from an OLE DB source containing three types of records.
The output records should look something like this:
Type 1 contains field1, field2, field3
Type 2 contains field1, field3, field6
Type3 contains field1, field4, field5

The source table contains all six fields.

If I simply map the input to output columns, I get all fields output in each record, which is not what I want.

So I run the source through a derived column transformation, inspecting the record type field and creating a new column containing the relevant fields concatenated together, e.g field1 + field 2 + field3 for the Type 1 record.

The problem is that the fields are now all scrunched together, i.e all the trailing spaces are being stripped out of each field during concatenation.

Is there any way to string these fields together while maintaining the original field length?

I'd like the output to look like
ABC FGH XYZ instead of
ABCFGHXYZ

View 7 Replies View Related

Concatenate 2 Sql Strings

May 2, 2008

Hi,
Whats wrong with the following T-SQL?

DECLARE @SQL NCHAR(4000)

SELECT @SQL = 'xxxx'
SELECT @SQL = @SQL + ' yyyy'

PRINT @SQL

I'm only seeing 'xxxx' in the debug window so it appears that its not adding the 2 strings?
Weird?!?

View 8 Replies View Related

Manipulating Strings

May 7, 2008

Hi All

I've got a list of IDs seperated by commas and I want to get each indivdual ID and insert them into a table. Has anybody got any ideas how this can be done?

Thanks

View 6 Replies View Related

Connection Strings

Nov 6, 2006

I have created a SSIS package, and got it to perform as I want.

I am having a problem though with connecting to my data source (OLE DB Source) using SQL Server Authentication.

If I use Integrated security, I have no problems. If I use SQL Server Authentication, I get errors.

Here is the connection string that works:

Provider=SQLNCLI.1;Data Source=.SqlExpress;Integrated Security=SSPI;Initial Catalog=MyDataBase

Here is the connection string that fails:

Provider=SQLNCLI.1;Data Source=.SqlExpress;Persist Security Info=True;Password=**********;User ID=myUserid;Initial Catalog=MyDataBase

The strange thing is that when I use the Connection Manager dialog screen and click on "Test Connection", I get the response that the connection succeeded. It is only when I try to run the task that I get an error.

Here is the error I am getting:

[OLE DB Source [1]] Error: The AcquireConnection method call to the connection manager "MyDataBase" failed with error code 0xC0202009.

[DTS.Pipeline] Error: component "MyDataBase" (1) failed validation and returned error code 0xC020801C.

I have eliminated the obvious errors (incorrect Userid or password). Also, I am unable to use Integrated Security as this package needs to be distributed to various systems that may or may not be on our company's primary domain.

It seems I am missing something really basic here, but am not seeing it.

Any thoughts?

View 12 Replies View Related







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