Is it possible to use a TRIM(){or similar} function when JOINing 2 tables via a NVARCHAR field in T-SQL?
I need to trim the field first because some of the data contains trailing spaces (GRRR) and so might future data...
In a table I have a coulmn of postal-codes where there is a space in the middle of the postcode. How do I do to trim them away?
View 6 Replies View RelatedHello all!This might be a newbie question, and there might be something Im just not thinking of right now, but I have a set of values that I need to get the average of but only from the middle 90%. Example:11 <-From here1234456 <- To here.7I thought I could solve it by subqueries and do the following:Select (((Select sum top 5 order asc) + (Select sum top 5 order desc)) - sum total)/rows*0.9 which would give me what I want, but I realised that when aggregating I cant order the subqueries.This is for an application (that will run the query on a sql-server) that only takes one query (although subqueries should be fine), and thats why I have a problem, I cant build any views or things like that.I guess my question is very simple: How can I get a sum of the bottom 5 percent without sorting descending?
View 9 Replies View RelatedI have a table Customer with 200000 records.
When I run query
DECLARE @posTable TABLE(idkey INT IDENTITY(1,1), value VARCHAR(128))
INSERT INTO @posTable (value) SELECT CustID FROM Customer ORDER BY CustID
At the first time it takes 1 minute 42 seconds, at the second time it only takes 3 seconds.
When i run the query
SELECT IDENTITY(INT, 1, 1) AS idkey, CustID INTO #posTable FROM Customer ORDER BY CustID
The order in column idkey is not exacts
Pls. tell me why? and How can i rewrite it.
is there a function trimming string such as Trim in vb?
View 2 Replies View RelatedIN ACCESS
UPDATE 14_Together_final_ICNs INNER JOIN 14_Together_UniqueSKUs ON [14_Together_final_ICNs].SKU = [14_Together_UniqueSKUs].SKU SET [14_Together_final_ICNs].sku_desc = [14_Together_UniqueSKUs].NewDesc
WHERE (((Trim(Nz([NewDesc])))<>""));
THIS IS WHAT I TRY IN SQL SEVER BUT GET A DIFFERNET RESULT
UPDATE [14_Together_final_ICNs]
SET sku_desc = [14_Together_UniqueSKUs].NewDesc
FROM [14_Together_final_ICNs] INNER JOIN
[14_Together_UniqueSKUs] ON [14_Together_final_ICNs].SKU = [14_Together_UniqueSKUs].SKU
WHERE (RTRIM(LTRIM([14_Together_final_ICNs].sku_desc))<> N'')
Guys/Girls
Have a small problem I have received a load of data in a flat file format and each column has dividend up with a comma. I have stuck the whole row in to its own column.
What I want to do is take the first 12 characters of the row and put them in there own column.
I think I need to use the Len function or the trim function, so can some body point me in the right direction on how to do this.
Regards Lee
Okay basic question from the village idiot. How do I return only the date and not the time from a datetime column and also can I return only a set number of digits in a float column?
Thanks in advance peter
The Yak Village Idiot
Hi I have the below paragraph in my datacolum called 'Description'
Ansmann Powerline 5 Super Fast Charger, Microprocessor controlled desktop charger with discharge function for up to 5 NiCd/NiMH cells, For all NiCd and NiMH rechargable batteries including AA/AAA/C/D/9V, Negative Delta V control, faulty cell detection, defined predischarge, trickle charge, separate status indicator for every charging slot, ultra rapid charging, 230V AC. For UK use. Price for 1 Charger.
How do I take only the sentence upto the first comma in my STORED procedure
(ex:Ansmann Powerline 5 Super Fast Charger)
Advance thanks
Vaishu
I'm writing a page that will return data from my database to the user based on their search paramaters, over several pages with 20 matching records showing per page, and a next button on the bottom. Similar to the format of any search engine.
However, I'd like to write this into the query, and I'm not sure how I would go about doing so. For example:
"SELECT TOP 20 to 40 * FROM Northwind"
Hopefully this makes sense. Is there any way of doing this?
Thanks in advance,Russ
I am trying to concatenate three fields into one in a View. I have the following data:
Last Name
First Name
Middle Initial (can be null)
I need my resultant field data to look like the following:
"Doe, John P."
I'm having a problem writing SQL that is sensitive to placing the period after the middle initial only if there is a middle initial present. If there isn't a middle initial, I just want the following: "Doe, John".
I have tried the following CASE statement:
CASE WHEN middleInitial IS NOT NULL THEN ' ' + middleInitial + '.' ELSE '' END
However, I get an error indicating that the CASE statement is not supported in the Query Designer.
How can I resolve this problem in a View? Is there a function similar to ISNULL(middleInitial, '') that would allow for the "."?
Lets say I have a column of type varchar and need to extract an integer value from the middle of it. The string looks like this:'this part is always the same' + integer of varying length + 'this part is different but always the same length'Is there a way to trim the constant string lengths from the beginning and end?
View 2 Replies View RelatedSo I have this query where i need to get the average date of about five different dates... Is there any way to do this or am I screwed. I looked at using the avg function but SQL server 2005 did not like that.
Thanks in advance for any help.
Im having a dumb moment... what is the easiest way to get the third and fourth character of a string???
Thanks :)
I need to display the middle initial from a name field that contains the last name, comma, and the middle name or initial.
Example data:
Jane,Smith Ron
John,Dow L
Mary Jane,Dow Welsh
The result I am looking for is to capture only the first letter of the middle name. In this data example, I would need to display the following on a separate column:
R
L
W
-- I have a first name field that sometimes contains only the first name,
-- sometimes contains the first name and the middle initial, and
-- sometimes contains the first name, a space, followed by NMI (for no middle initial)
-- how do I correctly grab the first letter of the middle initial in all cases?
-- I have been playing with patindex but its harder than I thought. guess I need a case
-- statement in addition to this. Any idea how I can do this?
-- thanks!
create table UHS_t1 (c1 varchar(20))
insert UHS_t1 select 'john a'
insert UHS_t1 select 'jeff b'
insert UHS_t1 select 'sue z'
insert UHS_t1 select 'joe nmi'
insert UHS_t1 select 'jamie'
select *, substring(c1, patindex('%[ ]%', c1)+1, 1) as middle_name
from UHS_t1
go
drop table UHS_t1
How do I stop the restore in the middle?
restore database blah
from disk='mybackups hismonthbackup1.BAK'
with replace, recovery, stats=5, maxtransfersize=1048576
khosara writes "I have one parameter @String with value "My name is Khosara".
How to get the value only "Is khos".
Could you please help me, witch method shold i use.
Thank in advance."
How do you get the following to work properly?
WHERE Location = 'John's House'
The apostrophe between n and s has me stumped. Thanks.
Hello The following command is for Name Split as First Name and Second Name. Please let me know based on the following only how to get Middle name. SELECT name,
CASE WHEN CHARINDEX(',',Name) > 0 THEN SUBSTRING(Name,CHARINDEX(',',Name)+1,LEN(Name)-CHARINDEX(',',Name)) ELSE '' END AS Fname,
CASE WHEN CHARINDEX(',',Name) > 0 THEN LEFT(Name,CHARINDEX(',',Name)-1) ELSE Name
END AS Lname
FROM YourTable
Some of the databases that I inherited contain search that are based on finding a string anywhere within a last name such as:
WHERE lastName like '%smith%'
It is desired that each of these names be returned:
Smith
Smithson
Nesmith
What is presently done is that updates to the last name fields trigger that substrings of the last name be sent off into a substring table wtih retention of no 2-char substrings. For these three last names the following would be kept:
(1) Smith, (2) mith, (3) ith and (4) th
(1) Smithson, (2) mithson, (3) ithson, ..., (n) on
(1) Nesmith, (2) esmith, (3) smith, ... (n) th
The where now becomes
WHERE lastNameSub like 'smith%'
This seems to make the search routine by last name faster. I would like to improve on this if I can. Suggestions?
Dave
Well, how do you do it? I've not seen any report on this... so could it be next to impossible?
Say you have 10 records, 1-10 and you want to insert a new record between record 4 and record 5 in a SQL database, just how do you code it? And oh, each record uses the auto ID increment feature.
we need to cut the aprostrophe out of a name (ie...o'brien, o'leary) to create userid's... can this be done....thanks!
View 1 Replies View RelatedWhat is the syntax of left triming characters seperated by space.
e.g elsie reed
and i want elsie to go to one field and reed in another field i do not want to use numbers because they don't all have the same numbers but are all seperated by spaces
What is the best way to trim a string to the space in a select statement? An example string would be:
'asdf and hjkl'
The result would be:
'asdf'
hi guys,
i need to update a column by trimming the right end. i tried many things but its not working. can anyone tell me?
Regards,
David
OK, the problem is like this; say the table is sumthing like this
i have a table 't3' has 3 fields
id(number,2)
date(date/time)
text(varchar,200)
eg. id date text
-----------------------
01 05-jul-07 abcdefghijkl (i want to get rid of the first 6 characters i.e. abcdef)
i want to display 'text' field from t3 table but with the first 6 characters trimmed/removed (i want to get rid of the 6 first characters)
so i did:
select LTRIM(text,6) from t3
but the above code didn't work because LTRIM only trims spaces NOT characters... Is there any way that i can trim off the first 6 characters from every record in the 'text' field?
In my report, i have a TextBox which retrieve the result from the database.I am using this code :
=Fields!Category.Value.ToString().Trim(" ").TrimEnd(",").TrimStart(",")
But i am encountering a problem,wherein,when the value contains '&' (ex. Jog & Hop)
the result became Jog&Hop.
What could be wrong with my trim???
Thanks!!
"select RTrim(columName) from tableName"
it can change the stirng " abc" to "abc". But I want change the string "a bc" to "abc".
Why the TRIM cannot work ?
How do I do ?Thanks.
Whan using the TRIM() function in a SELECT we are getting an invalid function error. What gives? I thought TRIM() was T-SQL & not one of Microsoft's SQL "extras".
Thanks!
can any one explain how to trim a value on bothside in rss
for eg:
'asd '
' asd'
i wanna this output 'asd'............
can i give this command
=Format(Fields!company.Value.Trim)
I need the values as below using select query of my column.
I need to find the text after the first space ' ' and before the first '('
Source Column
==================
0636 KAVITHI (LOC)
0638 SRI KRISHNA (NAT)
0639 SELVAM
0643 GOOD SERVICE (LOC)
0644 FINA CARE EVENT (LOC)
Expected Result
==================
KAVITHI
SRI KRISHNA
SELVAM
GOOD SERVICE
FINA CARE EVENT
Hello
Can any one tell me the difference between Cross Join, inner join and outer join in laymans language
by just taking examples of two tables such as Customers and Customer Addresses
Thank You