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%'
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 to extract a specific part of a string from a column in a sql server table. Following are the details and I have given the sample table and the sample strings.
I have 2 columns in my table [dbo].[StringExtract] (Id, MyString)
The row sample looks like the following
I have to extract the Id and a part of the column from mystring.
Id MyString 1 ABC|^~&|BNAME|CLIENT1||CLIENT1|20110609233558||BIC^A27|5014589635|K|8.1| ABC1|^~&|BNAME1|CLIENT1||CLIENT1|20110609233558||CTP^A27|5014589635|I|7.1| DEF||5148956598||||Apprised|Bfunction1||15|LMP|^^^201106101330| alloys3^ally^crimson^L||||alloys3^ally^crimson^L||||alloys3^ally^crimson^L|||||Apprised|
[Code] ....
The part I want to extract is in the line "ZZZ" and the string part that i want to extract is between the 5th and 6th pipes (|). So my output looks like the following
Id DesiredString 1 Extracts^This^String1 2 Extracts^This^String2 3 Extracts^This^String3
Is there a way to extract this either using TSQL or SSIS.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U')) DROP TABLE [dbo].[StringExtract] GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[StringExtract]( [Id] [int] NULL,
I am required to send an XML file of our clients to head office in Belgium for comparison against a database of known undesirables. The data is in a legacy system with a custom database so I have created an SSIS package that extracts the tables I need into SQL Server and have developed a program that reads from a text source and creates the XML then Secure FTPs it to Hong Kong who will handle it from there.
My problem lies in actually extracting enough data to avoid too many false positives. The scanning will check name, identity (passport number, etc.), town/city and country. We don't hold an identity number and the town/city and country are buried in free format fields. A quick analysis of the 419,000 records shows that the spelling is terribly unreliable, too. In most cases country has not been entered because the clients are local and even when they are overseas, sometimes only the city has been entered. That is often misspelt, too e.g. Kuala Lumpar or Melboure.
The addresses are held in 3 equal length fields called Address_1, Address_2 and Address_3. There's no guarantee that I will find the town/city or country in any particular one of these fields. In some cases, the street number and name are in Address_3 because the first two hold a company name and a C/O line.
So I'm not going to fret over the ones where the address information is nonsense or missing but I would like to try and extract valid country names and town/city names, where present and this is where I get stuck. I'm from a COBOL programming background and although I'm loving getting used to the power of SQL, I'm still a bit stumped when I come across a problem like this probably because I keep thinking of the solution in procedural terms.
I have a feeling that the solution will be to create two separate reference tables, one of towns/cities and the other of countries. I would then somehow search the 3 fields looking for those keywords and if found, entering them in the appropriate part of the output text file to represent town/city and/or country. I did also think about destringing to find the separate words but that doesn't help where the name consists of two words such as NEW ZEALAND.
I would love to hear from anyone who has dealt with a similar problem and has a neat solution to this using SQL.
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 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'
Consider the following: I have a table, say ORDERS, with these entries -
CustID ProductID 1 CAN 2 2 3 1,2 4 4 5 1,2,3,4,5,CAN 6 10 7 CAN 8 1,CAN
I'd like to write a script to return only those rows WHERE ProductID = CAN along with other values in the same column. In this example, I'd like to return rows 5 & 8. How can I write this in T-SQL? So, say, check if ProductID has a comma ',' value plus the 'CAN' string. If yes, then return that row. If I use the LIKE operator, it'll return rows 1,5,7, and 8.
SELECT CASE WHEN CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1) > 0 THEN REPLACE(Supervisor, SUBSTRING(Supervisor, CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1), LEN(Supervisor)), '') ELSE Supervisor END AS NewSupervisor from data d
However, I stumble when Middle Name exists somewhere in the name as Replace function repalces every occurrence of the string. For ex: "Allen, Gary G" becomes "Allen,ary"
Do we have any way to say sql to replace after certain index?
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.
We have 4 regions, currently we only have 3 servers in the field, and therefore only 3 regional id’s are being used to store the actual data of the pbx. The central server (RegionalID = 0) is holding the data for itself and the 4th region until the new server is deployed. It now has to be deployed and therefore the data migration for this region has to take place. I am trying to extract all the data for this 4th region (RegionalID= 1) from the central server database from all the relevant tables. When doing this I will firstly, have to check that the CallerID is valid, if it is not valid, then check that RegionalDialup = ‘0800003554’ which is the dialup number for this 4th region (RegionalID = 1).
I have a table named lnkPBXUser which contains the following:
I have a table named tblDialupLog which has 20 columns, I have selected only the columns I am interested in (below):
PBXIDDailupDT DongleAccessNum CLI RegionalID RegionalDialup 838/8/2006 8:58:11 AM T2 UQ 28924 013249370000800003554 5438/8/2006 8:55:44 AM T0 UA 33902 012362350000800003554 12198/8/2006 8:59:03 AM T3 ZD 02031 015295809500800003554 10128/8/2006 9:02:54 AM T0 UA 41261 017301105000800003554 13318/8/2006 8:59:57 AM T0 UA 01938 012460462700800003554 19798/8/2006 9:02:52 AM T0 UA 09836 016375121000800003554 19038/8/2006 8:58:41 AM T0 UA 26009 014717535600800003554 15228/8/2006 8:58:54 AM T3 MB 94595 057391287100800004249 3198/8/2006 8:51:28 AM T2 ZD 32892 054337510000800004249 32708/8/2006 9:04:26 AM T2 MB 8733100800004249
I have a table named tblCodes, it contains all regions but I only need to select the codes for RegionalID 1 :
CodeIDRegionalID ExtName SubsNDCDLocCDUpdateStatusRegionDesc 79731 PRETORIA 0123620NORTH EASTERN REGION 79741 HARTEBEESHOEK 012 30120NORTH EASTERN REGION 79751 HARTEBEESHOEK 01230130NORTH EASTERN REGION 79761 PRETORIA 01730140NORTH EASTERN REGION 79771 PRETORIA 01230150NORTH EASTERN REGION I have a table named tblDongleArea which contains the following (below only shows dongle area codes for the fourth region( RegionalID = 1):
Ok, I am dealing with the lnkPBXUser table at the moment,
I need to be able to join lnkPBXUser and tblDialupLog, then compare tblDialupLog.CLI to tblCodes.SubsNDCD + tblCodes.LocCD (when these two columns are concatenated the result will only be a substring of tblDialupLog.CLI. (this is to make sure that the CLI exists in tblCodes.)
If it does exist, then it is part of the fourth region and should be returned in the result set.
If it does not exist, I then need to check that tblDongle.DongleAreaCode is a substring of tblDialupLog.DongleAccessNumber.
If it is a valid DongleAreaCode for that region, then it is part of the fourth region and should be returned in the result set.
If it does not exist, I then need to check that tblDialupLog.RegionalNumber = ‘080003554’.
So from the above tables an expected result would be:
RegionalID pbxID userID 0 1012 17 0 543 2
Please assist, it would be greatly appreciated. Regards SQLJunior
As part of a data search project I need to be able to strip all non numeric characters from a text field. The field contains various forms of phone number in various formats. In order to search on it I am going to remove all non numeric characters from the input criteria and from the data being searched.
In order to do this I decided on using a SQL Server custom function: Pass in field. Loop through all chars, test against asci values for number range. return only numernic data concatenated into a string.
Are there any other more efficient ways of going about this?
I'm using sys.dm_fts_parser dynamic management function to tokenize a string of characters >4000. The function doesn't accept a query_string parameter >4000 characters. Is there a way around this? I've tried to execute the SELECT defined in the function but that doesn't work.
In my application I must store over 16000 character in a sql table field . When I split into more than 1 field it gives "unclosed quotation mark" message. How can I store over 16000 characters to sql table field (only one field) with language specific characters?
"Current Date updated ( 12/31/2015 -> 12/31/2016 )" "Status updated ( Open -> Should be Closed )" From example 1 ==> need outputs of " 12/13/2015" and "12/31/2016"; example 2 --> need outputs of "Open" and "Should be Closed"
These are what all records look like:
1. It contains the word, "updated". 2. There is a space after the last character of updated. Then, there is a space before open parenthesis. Then, there is a space before getting the required word. Example: "updated ( xxxxx" (xxxxx is a required extract word.) 3. The entire word(s) should be captured before the "->" sign (after the open parenthesis) 4. The last part is to captured everything after the "->" but before the close parenthesis ")"
I'd need to have a function that allows me to extract 'fields' fromwithin the stringI.E. (kinda pseudo code)declare @foo as varchar(100)set @foo = "Robert*Camarda*123 Main Street"select EXTRACT(@foo, '*', 2) ; -- would return 'Camarda'select EXTRACT(@foo, '*', 3) ;-- returns '123 Main Street'select EXTRACT(@foo, '*', 0) ;-- would return entire stringselect EXTRACT(@foo,'*' , 9) ;-- would return nullExtract( string, text delimiter, occurance)Anyone have something like this as a user defined function in SQL?TIARob
Now i want to find out all the project names which contain a particular substring in it, like say - "SRM" and update the "Flag" column to value 1 for all those projects.
I am trying to write a query in sql query analyzer that will extract a date that appears after the first comma in the string. An example of the data is below:
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?
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