I have an address field that has data that needs to parsed. The data has different formats. I have tried charindex and substring but I am not getting the results I need.
Here is what the data looks like:
Paper Mill Rd 3332
Allegheny Ave 13 1/2
Balto Nat'l Pike 6600 R-2
Rolling Rd N 3030
Here is the results I am looking for:
street dir street number street name street type street unit
3332 Paper Mill Rd
13 1/2 Allegheny Ave
6600 Balto Nat'l Pike R-2
N 3030 Rolling Rd
This script doesn't work. Why?UPDATE [460zz_Relma_Comment_Parse]SET [Comments 1] = LTRIM(SUBSTRING([Comments], 1,convert(bigint, CHARINDEX(',', Comments)-1)))WHERE NOT([COMMENTS] IS NULL) AND LEN([Comments]) > 8Basically, I'm trying to move everything before a comma into [Comments1] and everything after the comma into [Comments 2]. The comments 2script works. See below.UPDATE [460zz_Relma_Comment_Parse]SET[Comments 2] = LTRIM(SUBSTRING([Comments], convert(bigint,charindex(',', [Comments])+1), Len([Comments]) - charindex(',',[Comments])))WHERE NOT([COMMENTS] IS NULL) AND LEN([Comments]) > 8This script [Comments 2] runs perfectly but the first one [Comments 1]above gives me the following error.Msg 536, Level 16, State 5, Line 1Invalid length parameter passed to the SUBSTRING function.The statement has been terminated.They are basically the same script except I'm adding +1 to one scriptand minusing one -1 from the other.Thanks,db55
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%'
SQL Server 2000Ya know, it is always the simplest stuff that gets ya !!I am having the hardest time getting a simple piece of code working.Must be brain dead today.Goal: Get the users full name from a stringHere is sample data:"LDAP://blahblahblah/CN=Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"Code:IF LEN(@strReturnValue) > 0 BEGINSELECT @strReturnValue = SUBSTRING(@strReturnValue,(CHARINDEX('CN=',@strReturnValue)+3),(CHARINDEX(',',@strReturnValue)-1))ENDIt will extract:"Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"I want it to extract:Kevin JonesThanks.
Hi All,Just wondering if it's any more efficient to use the following SQLstatement to return a partial match from a column as opposed to thesecond statement.SELECT * FROM Table1 WHERE CHARINDEX('value', mycol) > 0Versus:SELECT * FROM Table1 WHERE mycol LIKE '%value%'Does anyone have any thoughts on whether or not either of thesestatements would return a result quicker than the other?Many thanks in advance!Much warmth,Murray
Hi, The following stored procedure creates this error, "Syntax error converting the varchar value '17,18,405,408,406,493' to a column of data type int." I tried using a Convert and a Cast function but it still failed. I then read something about using the CharIndex function, but I can't figure it out. Any help would be much appreciated in getting this to work. Thanks
CREATE PROCEDURE [ResourceProducts] @ProductHtml varchar(200) AS
SELECT Resources.ResourceId, Resources.Heading FROM Resources INNER JOIN Products ON Resources.ResourceId IN (Products.Resources) Where Products.ProductHtml = @ProductHtml GROUP BY Resources.Heading, Resources.ResourceId GO
Hi I have a column col1 in a table having below strings. How do I get only the required part of the string as I explaind below. (Needed strings are followed by i.e as below).
1. But the above code only works if the string starts with 'ID='. 2. Because the col1 has strings starts with different letter this code fail to excute and gives error 3. Pathindex with regular express '(patindex('%[^0-9]%',v)-1)' cannot be used because it gets anynumber in the string - string has mixer of numbers and letters
SO IF ANYONE LET ME KNOW HOW TO USE IF or CASE with 'charindex' or any other method to get the above said result would be greatfull.
UserID=176&StoreID=210 sometimes there is more between the two so im tring to use a CHARINDEX and Substring
Substring(Parameters, CHARINDEX('UserID', Parameters),3) When I do this it brings back just the 'USE' I need it to bring back the number associated with it. In this case the 176 how do i get that number?
I have used CHARINDEX successfully to search for a substring going from left to right in a string, but how do I search from the end of a string and go backwards?
Here is a sample of the problem I am trying to solve. Here is a list of cities, some with two names and some with more than two. I need a way to extract only those cities with two names. I wanted to do this by locating the first space in the string and the last space in the string. If the first space equals the last space, then the city should only be a two word city. Otherwise it is a three name city or greater. So what string functions can I use to locate the first and subsequent spaces in a string?
Here is the current version of my SQL statement:
SELECT City.City, CHARINDEX (' ' ,City.City) AS First_Space FROM City WHERE CHARINDEX (' ' ,City.City) <>0
AND HERE IS A SMALL SAMPLE OF WHAT IS RETURNED:
MARINE CITY7 MARINE ON SAINT CROIX7 MARION CENTER7 MARION HEIGHTS7 HIGH RIDGE5 HIGH ROLLS MOUNTAIN PARK5 HIGH SHOALS5
Thanks to all who understand and can offer usable advice in my dilemma.
I have a field containing a string with '/' in it multiple times.How can I return the charindex of the last occurance of '/' in thestring?Regards,Ciarán
What is the best way to essentially use the charindex(find) functionif the value is could be more than one variable (A or B or C)I can't seem to get an "or", "if" or "select if" to workBelow is the code that works, I need 'GOV' to be either 'GOV' or 'FWD'or 'LIB'sqlstring = "SELECT Distinct substring([exposurename]," _& Len(Worksheets(4).Range("j5") & "_VAR_" _&Worksheets(4).Range("C4").Offset(Worksheets(4).Range("c3"), 0)) + 2 _& ",charindex('GOV',[exposurename])-" &Len(Worksheets(4).Range("j5") _& "_VAR_" &Worksheets(4).Range("C4").Offset(Worksheets(4).Range("c3"), 0)) + 3 _& ") AS Drivergrp2 " _& "FROM mars.dbo.mroInventoryProductGreeks_Latest" _& "Where producttype = 'creditdefaultswap' " _& "AND exposureName like '" &Worksheets(4).Range("j5") & "_VAR_" &Worksheets(4).Range("C4").Offset(Worksheets(4).Range("c3"), 0) & "%' "_Much appreciation if you can help
Hello,I need to be able to replace only the first occurance of a space characterin a column.Reason being is the data in the column I am trying to replace seems to haveumpteen space characters after each entry, so a simple replace functionwill replace all the spaces after it with what I want!I have thought of RTRIM to get rid of the spaces after and then replace, Ihave also thought of CHARINDEX to find the first occurance of a space andSTUFF to replace it.I have done my homework on these functions!But I am having trouble writing such a statement,I've never written a query which would use more then one function on onecolumn you see and I am getting confused!I'll tell you what I want to do in simple stepsReplace only the first found space in a name column, but then if a name hasa middle initial that will be a problem,Replace that with a dot.then concatanate '@emailaddress;@emailaddress2' after itso when SQLServer does the select it will bring back something likejoe.bloggs@emailaddress;emailaddressBut I guess I'd also need joe.n.bloggs@emailaddress;emailaddressThe data in the column looks like this at the momentjoe bloggsBut I guess there may come a time when we havejoe n bloggs, just to complicate things!What is your advice, and how do I write a query like thisI have been playing around with it in Query Analyser but as I said I amgetting confused and need some help if you don't mindThanks a lot to all who reply :-)RegardsJayne
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!
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'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?
Code: DECLARE @searchString nvarchar(100) SET @searchString = 'sample' SELECT TOP(1) * FROM user WHERE (user.identity LIKE @searchString OR CHARINDEX(@searchString, user.firstname + ' ' + user.lastname) > 0) PS: Handwritten...
If I pass in a searchString that matches the Identity and a different users lastname, this query will return the user with the lastname-match (which is wrong in my eyes, it should have matched the Identity first, then returned that row [Identity is a Primary key, indexed non-clustered]).
I've tried various things: Removing LastName: (CHARINDEX(@searchString, user.firstname + ' ') > 0), then the returned row is from a matching identity, due to the lastname of a user was a match, but lastname was removed...so :P Flipping the conditions around Adding/removing paranthesis... without any luck.
Is there some option somewhere, to force the OR statement to return on the first true condition. I've always thought OR-statements (in a computer that is) breaked and returned true on the first true condition it found (from left to right, not bother to check the rest of the conditions...)?Or do I have to rewrite the query, with an IF, checking if the @searchString is a valid identity-format, if it is, query on the identity, if not query on the username...?
I have a column that has 50 records and they are INT. I am trying to place them into a single parameter using the CHARINDEX using the IF Statement. I got an error saying convertinh from varchar to int type data.
Basically, this is what I did: @RoomID varchar(max)
Then I declare it:
DECLARE @RoomId varchar(4)
SET @RoomID = 0
The IF part that I need to express this CHARINDEX function.
Then in Where clause I filtered it as: WHERE r.roomid = @RoomId
Or what other way to handle INT data type and make them available for user to choose multiple value?
Hello to all, I hope that somebody can help me. I have written a sql query to search Partner. I have a wtcomValidRelationships Table. There are two Fields (IDMember(type: Int) and RelationshipIDs(type: varchar(1000)) in this table. Example: 3418 has 3422 RelationshipID and 3422 has 4088 RelationshipID, if i want to check if there is a relationship between 3418 and 4088. declare @IDM int; declare @IDO char(100); set @IDM = 3418; set @IDO = '4088'; select B.IDMember from wtcomValidRelationships as A, wtcomValidRelationships as B where A.IDMember = @IDM and charindex(cast(B.IDMember as char(100)),A.RelationshipIDS) > 0 and charindex(@IDO,B.RelationshipIDs) > 0 Using this query i get nothing. I try to use constant in charindex and i get result. declare @IDM int; declare @IDO char(100); set @IDM = 3418; set @IDO = '4088'; select B.IDMember from wtcomValidRelationships as A, wtcomValidRelationships as B where A.IDMember = @IDM and charindex('3422',A.RelationshipIDS) > 0 and charindex('4088',B.RelationshipIDs) > 0 So i think that charindex doesn't work with variable. But I must use variable. Can someone help me? What should i do ? Thanks Best Regards Pinsha
I’ve got an issue with extracting specific data from one field using charindex. Here’s an example of the dataset:
Cl_nr Var_data
20059942 ?;;300134BL10;?;;;;;; 20059958 ;2698;020225PU20;?;;;;;; 20059975Â Â Â Â ;;100777ST20;?;;;;;; 20059976 ;;;;;;;;; 11001980 ;;051168PU20;?;;;;;;1001980 20034832 ;;060253BO10;?;;;;;; 20055246 ;;1108731;?;?;;;;; 20043656Â Â ;;1022509;?;;;;;; 20059366 ;;1181750;31-12-2015;2;;;;; 20052712Â Â Â Â ;;230626NO10;?;;;;;;
Goal is to get the data after the 2<sup>nd</sup> ;Â until the next ; starts. Ideal would be to catch everything between the 2<sup>nd</sup> ; en 3<sup>rd</sup> ; (number should be 10 characters).
If I try to select this data just using charindex it only goes until it finds the first ; (of course), what’s the best approach in this?
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.
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.
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.
Hi all, I have just started using SQL7 and quite dumb at it. Here is my problem
i have tables ip_address and ip_subnets. both contains more than 20,000 records. Though ideally each subnet should correspond to only one ip address it is not so due to SMS inventory and remote clients configurations etc.
As an example If my ip address is 141.151.128.78 I need to select only 141.151.128.64 ( or atleast 141.151.128.*)as the valid subnet. In other words I need to compare upto 3rd octet and only if it matches with ip address then declare that as the valid subnet. Pls note that ip addresses vary for each machine though there will 4 octets, I can't use character positions as the nos in each octet might vary from 1-255.
Any help would be greatly appreciated Pasted here under is the query script I am playing around with charcter poistions which are not working in my favor. Just added to explain my problems in more clearer way select distinct system_data.name0, System_IP_Address_ARR.ip_addresses0, System_IP_subnets_Arr.ip_subnets0, system_disc.client0 from system_Data, System_IP_Address_ARR, system_ip_subnets_arr, system_disc where system_data.machineid = System_IP_Address_ARR.itemkey and system_data.machineid = system_ip_subnets_arr.itemkey and system_data.machineid = system_disc.itemkey and system_disc.client0 = 1 and substring(System_IP_Address_ARR.ip_addresses0,1,10 ) = substring(System_IP_subnets_ARR.ip_subnets0,1,10) and substring(System_IP_Address_ARR.ip_addresses0,10,1 ) = '.' and System_IP_subnets_ARR.ip_subnets0 not in ('11%.%.%.%','12%.%.%.%', '10%.%.%.%' , '10.%.%.%' , '1.%.%.%') order by System_DATA.name0
