street_name
---------------------
1A HAYES ST
11a yONU STREET
i need to parse off watever is in front of the street_name. anyone
has any approach>?
street_name
-------------
HAYES ST
yonu street
After reading performance tuning articles and stuffs. Im getting confussed about writing my queries. One point says don't use 'not in' instead use 'not exists'. But looks i cant implement that in my query.
I'm trying to get only the decimal numbers. not the fractions.
my question is if this is the best query? Inputs will be appreaciated.
declare @table table (tableid int identity, ad_str1 varchar(100))
insert @table
select '2 NORTHPORT AVENUE' union all
select '22 NORTHPORT AVENUE' union all
select '233 NORTHPORT AVENUE' union all
select '2433 NORTHPORT AVENUE' union all
select '2 1/2 NORTHPORT AVENUE' union all
select '22 1/3 NORTHPORT AVENUE' union all
select '233 1/4 NORTHPORT AVENUE' union all
select '2433 1/8 NORTHPORT AVENUE' union all
select '2a NORTHPORT AVENUE' union all
select '22a NORTHPORT AVENUE' union all
select '233a NORTHPORT AVENUE' union all
select '2433a NORTHPORT AVENUE' union all
select '2 a NORTHPORT AVENUE' union all
select '22 a NORTHPORT AVENUE' union all
select '233 a NORTHPORT AVENUE' union all
select '2433 a NORTHPORT AVENUE' union all
select 'a NORTHPORT AVENUE' union all
select '15 e NORTHPORT AVENUE' union all
select '15 n NORTHPORT AVENUE' union all
select '15 s NORTHPORT AVENUE' union all
select '15 w NORTHPORT AVENUE' union all
select '15 c & k north avenue' union all
select '1/3 NORTHPORT AVENUE' union all
select '93 H ROAD'
select tableid, ad_str1, left(ad_str1, charindex(' ', ad_str1))
from @table
where left(ad_str1, charindex(' ', ad_str1)) not in ('1/2','1/3','1/4','1/8','2/3','3/4')
and isnumeric (left(ad_str1, charindex(' ', ad_str1))) = 1
sometimes a capital N is added in front of a string, for example,
N'xxxx'.
What does it mean?
HI, i am working on ASp.net web applicaton , i am using the following connection string in my web.config file. <add name="X_Conn" connectionString="Data Source=XXX;Initial
Catalog=XXX;User ID=XX; Password=XXX;"
providerName="System.Data.SqlClient"/>
How can i get this connectionString attribute value from the front end.
The issue being we have a category field that has Business;Client;Stuff. I would like to remove just the Business and leave the other stuff. Ive tried the link below and it removes everything..
update dbo.TBL_CONTACT
set CATEGORY = REPLACE(CATEGORY, 'Business', NULL)
TableA
Col1
----
13.4
13.4 a
13..4
13,.4
Result for Table A needed:
Col1 ColCalculated
--------------------------
13.4 13.4
13.4 a null
13..4 null
13,.4 null
how can I achieve it.
Hi,
In a extract that i have the SS# is as 123-45-6789 how do i remove the dashes in the string and have the final expression of 123456789.
thanks
Karen
There are unwanted characters(''','/','&'.. etc) in column.
I need to remove these characters
Hi All,
I have one field with different values like
F1
A,B,C,
D,E
G,H,I,
My requirement is to remove the last comma from that string. I need the output should look like
F1
A,B,C
D,E
G,H,I
thanks,
Mears
hi, im running into a bit of a problem. i will like to string parse the street name into what you see below using DTS. How can i get around this?
12 north plaza boulevard apt.16
12|N|plaza blvd| apt16
Hi,Does anyone have a function which replaces accent chars from a stringwith the non-accented equivalent? For example 'hôpital' should return'hopital'.Thank you in advance.
View 3 Replies View Related
I have a column of string which has html tags attached to it. How can I remove them..other than manually going and doing it? Any funtions?
Thanks!!
Tanya
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
I'm thinking of doing some basic parsing of address. I want to seprateout the house number from the street name from the suffix. I have thesuffix's from the USPS, but I cant find a database of US street names.Anyone come across one or know where I can get one?TIARob/end off topic
View 2 Replies View RelatedHi all, a bit of a nasty one here!Ive inherited a server that has just collapsed. It contains SQL Serverand has a fair amount of data on it.The server would only boot into a BSOD Stop Error. I suspected thatone of the hives in the registry was corrupt so I used the Win2KRecovery Console to restore the registry to its original settings (SoWindows can not see the SQL stuff now). There is no restore pointsfrom which to take recent registry settings.My Question is this...We have a spare server (just lying about you know!!!) which we plan toinstall Win2K and MS SQL Server onto... How can I transfer the data,in a nice easy manner, from the old server to the new server?Is it possible to move just the data files into the new install?Remember I inherited this server! (I dont have Backups!!!)Please... No email's about how important backups are... I know howimportant backups are.Any help/ideas would be appreciated.Thanks, John T.
View 3 Replies View RelatedMy sample data is as below.
plan type A change from plan type B from Plan type C
Insurance plan M changed from Insurance plan b From plan d from Plan N
Now from above strings i want to remove all data from second appearance of from. i.e. i want display values as below.
plan type A change from plan type B
Insurance plan M changed from Insurance plan b
I have a field that contains some text. Each field will have none or atleast one comment in it. A comment can be a string of any length with *** on both sides. Ex: ***comment***
Declare @Test_tbl Table(TextField Text)
Insert Into @Test_tbl
Select 'Some text ***comment*** some more text' Union all
Select 'Other text ***another comment*** more and more text' Union all
Select '***Comment*** some text ***More Comments***' Union all
Select 'some text with no comment'
I need the output be ...
TextField
--------------
Some text some more text
Other text more and more text
some text
some text with no comment
Thanks in advance.
Looking at a trace table and trying to remove all the "erroneous" bits in a string e.g the declares etc so I can purely get to proc names.
An example string
declare @p2 varchar(10) set @p2=NULL exec sp_proc @Code='TF',@TypeCode=@p2 output select @p2
I've tried
select top 5000 textdata,substring(textdata,charindex('exec',textdata)+5,charindex('@',textdata)-1)
from trace_table
where TextData like '%sp_%'
and TextData like '%declare%'
And it fails dismally...
I want to remove special characters from a string in sql like <?> in a column value in a table.
View 1 Replies View RelatedHow can i perform this task with ssis  OR TRANSACT SQL? I HAVE THESE ROWS WITH THE NEXT DATA, I want to take just the valid one, BUT I HAVE A LOT OF COMBINATIONS AS following names, it can be animals, things or personal names
GABRIEL OBANDO --CORRECT
GABRIEL OVANDO
Gavriel OVANDO
gAbriel OBANDO
GABRIE OBANDO
Gabri OBONDA
MANAGUA --CORRECT
NANAGUA
NAMAGUA
Hi to all,
I am having a string like (234) 522-4342.
i have to remove the non numeric characters from the above string.
Please help me in this regards.
Thanks in advance.
M.ArulMani
Hi to all,
I am having a string like (234) 522-4342.
i have to remove the non numeric characters from the above string.
Please help me in this regards.
Thanks in advance.
M.ArulMani
I have a SQL select syntax as below
0 AS SalaryMin,
2088 AS SalaryMax,
2088 AS BillableHours,
'Month' AS SalaryPaidCode,
0 AS SalaryBreakdownHourly,
0 AS SalaryBreakdownDaily,
[Code] ...
While outputting to CSV.file
I got :0,2088,2088,"Month",0,0,0,0,0,0,0,"N/A","N/A","G","N/A","Exempt","Other",1
How can I remove all double quotes in the string fields? so that O can get the result as below while the output
0,2088,2088,Month,0,0,0,0,0,0,0,N/A,N/A,G,N/A,Exempt,Other,1
I am querying with a SELECT statement against an address table that has a post code column with corrupt data.
Sample record:- Northants NN4 0NB
Should be NN4 0NB
I have used the following on another column:-
LEFT(A.Addr1,CASE WHEN CHARINDEX ('(', A.Addr1) =0 THEN LEN(A.Addr1) ELSE CHARINDEX('(' ,A.Addr1) -1 END) AS 'Address 1'
but unable to correct this problem?
I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'
Example Record 1. Elland **REQUIRES BOOKING IN***
Example Record 2. Theale, Nr Reading, Berkshire
Example Record 3. Stockport
How do I achieve this in a CASE Statement?
The following two case statements return the correct results, but I some how need to combine them into a single Statement?
,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0
THEN LEN(Address )
ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'
,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0
THEN LEN(Address)
ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'
I'm presented with a problem where I have a database table which must be migrated via a "custom tool", moving the data into a new table which has special character requirements that didn't exist in the source database. My data resides in an SQL Server 2008R2 instance.
I envision a one-time query which will loop through selected records and replace the offending characters with --, however I'm having trouble understanding how this works.
There are roughly 2500 records which meet the criteria of "contains bad characters", frequently containing multiple separate bad chars, and the table contains roughly 100000 rows.
Special Characters are defined as #%&*:<>?/{}|~ and ..
While the field is called "Filename" it isn't always so, it is a parent/child table where foldernames are also stored.
Example data:
Tablename = Items
ItemID Filename ListID
1 Badfile<2015>.docx 15
2 Goodfile.docx 15
3 MoreBad#.docx 15
4 Dog&Cat#17.pdf 15
5 John's "Special" Folder 16
The examples I'm finding are all oriented around SELECT statements, to change the output of what I see returned, however I'd rather just fix the entire column using an UPDATE. Initial testing using REPLACE fails because I don't always have a single character as the bad thing in a string.
In a better solution, I found an example using a User Defined Function to modify the output of a select, but I cannot use that UDF in an UPDATE.
My alternative is to learn enough C# to modify the "migration tool" to do this in-transit, but I know even less about C# than I do of SQL.
I gather I want to use @@ROWCOUNT to loop through the rows but I really can't put it all together in a cohesive way.
Hi all,
I tried to remove AdventureWorksDB in the "Add or Remove Programs" of Contol Panel and I got the following errors: (1) AdventureWorksDB Error 1326: Error getting file security: CProgram FilesMicrosoft SQL ServerMSSQL1MSSQLGetLastError: 5. |OK| and (2) Add or Remove Programs Fatal Error during installation (after I clicked the |OK| button). Please help and tell me how I can solve this problem.
Thanks in advance,
Scott Chang
I have uninstalled the CTP version of the SQL Server express so that I can install the released version but CTP version is still listed in the add/remove program list but without the change/remove button. I have been to different sites to find information on cleaning this up and I have ran all the uninstall tool I can find but the problem still prevails. I cannot install the released version without completely getting rid of the CTP version. Please help anyone.
Thanks
deebeez1
I need help,
I am having a hard time removing my SQL instance inside the Add/Remove program. After i select the SQL Instance name and then I tried to remove it but it won't allow me to delete it. There isn't any error message or whatsoever. Actually, when i try to log it in my SQL Management studio, that certain sql instance name is not existing according to the message box. Is there any way to remove the Sql Instance in my system?
I appreciate your help, Thanks
IS Support
I have a table that has a street number field.
if the user types in a street number of '2' i would like to return all street numbers the begin with 2 (2,20,21, 200, 201,205,2009,...)
how can this be done.
Sorry for the lengthy question, but here goes:
I currently have an access database with a FE and BE that resides on each users laptop. Weekly they connect to the network and synch the BE the master on a network drive. There are numerous propblems with this as it has grown quite extensive.
We would like to have this be a server based database with the remote users either having access through the web or something else?
A few of the capabilities of the current database:
1. generate and print reports 2. update project forms. 3. enter time logs 4. enter notes
My question is:
What are my options for a remote FE? I am well versed in VB6 and Access. Would be willing to learn other languages (VB.net, my SQL, etc.) Would I be able to have the same functionality or would I have to have separate software for certain functions?
I have a stored procedure that can take up to 2 hours to run, has anyone any ideas on what front end would be best used to call it
View 3 Replies View RelatedHi,
If I don't have any visual studio design tools etc.
Is there any way I can build a simple form that will allow users to view, insert, update and delete single rows from a SQL 2005 database?
Or is there any free downloads that might help. the table is really simple and has only 4 fields
TIA