I am trying to erase some erroneous bad data in my table. The description column has a lot of </div>oqwiroiweuru</a> weird data attached to it, i want to keep the data to the left of where the </div> erroneous data begins
update MyTable set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid = 1
that totally works.
update MyTable set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid >= 2
gives me a Invalid length parameter passed to the LEFT or SUBSTRING function. The statement has been terminated error.
Hello,I was hoping someone could help me with this SQL statement.The column 'options.optionDescrip' is a varchar field.Some values of the 'options.optionDescrip' contain commas, some donot.We are trying to evaluate against any data in the'options.optionDescrip' column located to the left of a comma (if acomma does exist, which it may not). If no comma exists, then we tryand evaluate against the entire field, not just the part to the leftof the comma.SELECT options_optionsGroups.idProduct FROM options_optionsGroupsJOIN Options ON options_optionsGroups.idOption=options.idOptionJOIN products ON options_optionsGroups.idProduct=products.idProduct WHERE (CASE WHEN CharIndex(',',options.optionDescrip) = 0 THENoptions.optionDescrip LIKE '" & gauge & "%' ELSELeft(options.optionDescrip,CharIndex(',',options.o ptionDescrip)) LIKE'" & gauge & "%' END)Thanks for any input you can provide, I appreciate it.
I have a stored procedure that receives a list of ids, to get the emails of each of those ids. The problem that I'm having is that I'm using a char data type that is max length is 8000, but the contact lists are getting bigger and soon they might reach the variable max length. The contact list that I receive will look something like this "1234,67523,67875,789687,", I'm using "," as a separator. Right now what I do is this @array_value = LEFT(@ContactList, @separator_position - 1)
The LEFT function doesn't work with data types text and ntext. But I'm in need of a string data type with a max length bigger than 8000. So I will apreciate if anyone knows of another function that does the same or similar and works with text data type, or any other ideas will be welcome.
HelloI am quite hopeless and of course a newbe.The situation: Sql2k / queryI would like it ot break down the following string:2004 Inventory:Ex.Plant Farm1:1st Cut:Premium:0094Whereby:Year = '2004 Inventory'plant= 'Ex.Plant Farm1'cut = '1st Cut'grade = 'Premium'lot# = '0094'It is always seperate by ':', but it can be 5 fields or 4 or 3 and sooncode to create the view:CREATE VIEW dbo.TESTASSELECT FullName, LEFT(FullName, CHARINDEX(':', FullName + ':') -1) AS year, CASE WHEN LEN(FullName) - LEN(REPLACE(FullName, ':', ''))[color=blue]> 0 THEN LTRIM(SUBSTRING(FullName,[/color]CHARINDEX(':', FullName) + 1, CHARINDEX(':', FullName + ':',CHARINDEX(':', Fullname) + 1) - CHARINDEX(':',FullName) - 1)) ELSE NULL END AS Plant, CASEWHEN LEN(FullName) - LEN(REPLACE(FullName, ':', '')) > 1 THENLTRIM(SUBSTRING(FullName,CHARINDEX(':', FullName + ':', CHARINDEX(':',FullName) + 1) + 1, CHARINDEX(':', FullName + ':', CHARINDEX(':',Fullname) + 1) - CHARINDEX(':',FullName+':') - 1)) ELSE NULL END AS [Cut]FROM dbo.ItemInventoryCan anyone help me with this? I am stuck half the way and get for cutthe rest of the string: '1st Cut:Premium:0094'Thanks!
I've the following query. I'm using the yellow highlighted to join 2 tables, as these tables dont have a relationship between them. The format of the name field is 'AAAA-BBBBBB-123' here A will be only 4 chars, followed by '-' B can be any number of chars again followed by '-' and the last is the id which I'm using to do a join. This query will fail if the id is just 1 digit as its taking the last 3 chars as the id. I dont know how to get the id from the right using charindex. Charindex would search for the first occurence of '-' from the right and get the chars after hypen i.e 123. How can this be achieved?
SELECT id AS 'ID', name AS 'name', sequence AS 'num' FROM FirstTable A INNER JOIN SecondTable q ON (CONVERT(INT,RIGHT(name,3))= a.id) INNER JOIN ThridTable t ON(t.id = q.id) INNER JOIN FourthTable s ON (q.name = s.name ) WHERE A.id = @ID AND t.name=LEFT(s.name,((CHARINDEX('-',s.name))-1)) ORDER BY 'ID','num'
One more question on this is: Is this a good way of joining tables? If I dont use this I've a very large query containing unions. Which one should be bug-free and more efficient?
I have a column that contains the follwoing string I need to compare.
ek/df/cv/ ek/df/cv/f
All fields bfore the third / are not fixed but behind the third/ is eiter nothing or one letter I need a function to extract all the fields before the third / to compare if they are equal.
I can't do it by using the combination of Substring() and charindex() and Len()
select top 5000 textdata,substring(textdata,charindex('exec',textdata)+5,charindex('@',textdata)-1) from trace_table where TextData like '%sp_%' and TextData like '%declare%'
I have a list of ClassID that is stored based on users multi select on a listview
For example ClassID might contain
301 302 303 304
Now I need to find InstructorID where classID matches all the value in the above list.
I am using this query
Code: Dim assSQL = "Select InstructorID from ClassInstructors where ClassID = @P0" For i = 1 To classIDs.Count - 1 assSQL &= " UNION Select InstructorID from ClassInstructors where ClassID = @P" & i.ToString Next
[Code] ....
But the problem is the query is returning InstructorID where ClassID matches any of the ClassIDs. I want it to return Instructor ID where ClassID matches all of the ClassIDs in the string.
I have written a query to search for a string in an expression by the number of it's appearance. Script is like this:
DECLARE @Expression VARCHAR(8000) = 'abcd_e_fgh', @SearchString VARCHAR(10)= '_', @OccuranceNumber SMALLINT = 1 DECLARE @SearchIndex INT = 0, @SearchIndexPrevious INT = 0, @Sno INT = 0 WHILE @Sno < @OccuranceNumber BEGIN
[Code] .....
Here i'm trying to search "_" in expression "abcd_e_fgh" where it is appearing for first time. it gives me 5 correctly. Now when i change the @OccurenceNumber to 2 or 3, it gives correct values 7 and -1 respectively. However now when i change it to 4, it gives me 5. So when it's trying to check for fifth appearance of "_", it's not actually giving 0 or -1 but repeating the value 5.
Could someone please help me? I am trying to pull in a partial string (the last six characters of the field, to be exact).
This is an example of my code:
select * into #temp_2 from #temp_1 a, Server2.DBa.dbo.table2 r where r.field1r = a.field1a and r.field3r = a.field3a (field3a is where I need just the last 6 characters)
To be more specific: r.field3r looks like 000884 a.field3a looks like 17445000884 So- I just want to pull in the 000884 off of a.field3a
I am looking for an expression for a group in a matric. I am trying to figure out how to group by the a certain amount of letters in a string. For example if I have the followong fields I am grouping...
Bob001
Bob
Robert005
Doug053
Doug100
Douglas
Barney001
Frank
I want to group it up as...
Bob
Doug
Barney
Frank
And then be able to summarize the results in the matrix.
Is it possible using Sql2000 to compare the first four characters of a parameter? WHERE (tblLinkInfo.CG_ID = @BCG_ID) AND ( 'first 4 characters' of Location_Zip.Zip = 'first 4 characters' of @ZipCodeIn)
Sorry, I realize that this is probably a pretty simple question for you, but I am in a quick time bind and would really appreciate it if someone could help me with this update that I'm trying to run.
Let's say you have a table (call it testtable) that contains the column "text".
Now in column text you want to replace all instantiations of the phrase "in the US" with "to the United States", how would you structure your update query to perform this change (Please note that this phrase could be embedded in the middle of a larger sentence like 'Send a package in the US'. You'd want to change this instance to 'Send a package to the United States')
Daily, I have to map thoursands of items into its correct category. What I have to do is Filter item by the ClSID and SCLISD, then look into the Label for where the item should be. For example from the attachment,
CLSID = 1 and SCLSID = 23 - if in the label has the keyword "notebook", its NEW_CAT should be 14 and its NEW_CATNAME is LAPTOPS;
CLSID = 1 and SCLSID = 23 - if in the Label contains Tablet PC, its NEW_CAT name should be 2474 and its NEW_CATNAME is PC LAPTOPS.
CLSID = 3 and SCLSID = 54 - if in the Label contains Watch, its NEW_CAT name should be A6052 and its New_CATNAME is SMART WATCH.
Basically, the work flow is looking into the CLSID, then SCLSID,then, Label and then assign a correct NEW_CAT and NEW_CATNAME of the item.
I have written a code but I can't get it look into specific CLSID and SCLSID.
For example, i have when upper(Label) like upper ('%Tablet%PC%') then '2474 PC Laptops'
I have a table with DiscNo, Artist, Title and other fields. I would like to find all duplicate records with the same artist/title and with the first 3 characters of the discnumber. e.g.
SELECT Artist, Title Into #TempArtistTitle FROM MediaFile GROUP BY Artist, Title HAVING COUNT(SubString(DiscNo, 0, 3)) > 1
SELECT MediaFile.DiscNo, MediaFile.Artist, MediaFile.Title FROM MediaFile RIGHT OUTER JOIN #TempArtistTitle ON MediaFile.Title = #TempArtistTitle.Title AND MediaFile.Artist = #TempArtistTitle.Artist ORDER BY Artist, Title, DiscNo
Drop TABLE #TempArtistTitle GO
See, if the first 3 characters of the disc number is the same, it is the same manufacturer. This query works somewhat, although it returns records that the discnumber is unique too. Like below, the LG disc number shouldn't be returned, as there is only one record for that artist/title.
SC8151-10 - Garth Brooks - Friends In Low Places SC8125-04 - Garth Brooks - Friends In Low Places LG5003-07 - Garth Brooks - Friends In Low Places
I have a table that lists states that I need to see order info from. I am attempting to create a SQL query that displays the production data for those states. So if the state is in my saleState table I want it to show in my query result even if there were 0 sales to that state. Below is the syntax I am trying, but if the count is 0, it is not returning the sales state it is omitting it.
Code: Select a.state, COUNT(b.recordID) From saleState a left outer join tblSales b On a.state = b.state And b.orderDate between '01/01/2012' AND '12/31/2012'
The select command below will output one patient’s information in 1 row:
Patient id Last name First name Address 1 OP Coverage Plan 1 OP Policy # 1 OP Coverage Plan 2
[code]...
This works great if there is at least one OP coverage. There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table. The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill. The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date.
select src.pat_id, lname, fname, addr1, max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1, max(case when rn = 1 then src.policy_id end) as OP_Policy1,
i am having names like AB_12 I want to get all rows with left part similar , AB im that case
SELECT id, name FROM Users WHERE LEFT(name, CHARINDEX('_', name) - 1) AS name IN ( SELECT LEFT(name, CHARINDEX('_', name) - 1) AS ns FROM Users GROUP BY LEFT(name, CHARINDEX('_', name) - 1) HAVING (COUNT(*) > 1) )
does not work
is there any way to use a variable ?
declare @nm nvarchar set @nm = SELECT LEFT(name, CHARINDEX('_', name) - 1) AS ns FROM Users
The Orders table contains orders placed on all the dates. I want to obtain a list of orders for a particular date, if there is no order for a product on the requested date, I want to return null values for the Quantity and Price fields.
I tried the following select statement:
select Products.ProductName, Orders.Quantity, Orders.Price from Products left join Orders on Products.ProductID = Orders.ProductID where Orders.OrderDate = '10/16/2004'
Where, there are a total of three products (A,B,C) in table Products. Product-C has no order on 10/16/2004, but I want it to return :
select left('Hello World /Ok',charindex('/','Hello World /Ok')-1)Hello WorldThat works fine.However I got an error message:select left('Hello World Ok',charindex('/','Hello World Ok')-1)Instead of:'Hello World Ok'I get:Server: Msg 536, Level 16, State 3, Line 1Invalid length parameter passed to the substring function.Microsoft Doc incorrectly says:"LEFT ( character_expression , integer_expression )integer_expressionIs a positive whole number. If integer_expression is negative, a nullstring is returned."Is there an easier solutoin using left or any other string functioninstead of using a case statement?Also, charindex('/','Hello World Ok') should return NULL instead 0 sothat we can use isnull function.Thanks.
i have a column name remarks and i want to retrieve data like by deleting some of the leading charaters and some last characters and show the middle one.
like remarks has CALLTRANSFER_OVER_SIP:XfrTime=86.05599975585938_en and i want to show only "XfrTime=86.05599975585938" this much