easy way to extract a substring that is between 2 characters?
Example string: 123n_abcn_123n
The substrings before and after the underscores can be any length.
My objective is to be able to extract the "abcn" from a column of values.
SQL Query. What i need is to be able to extract specific characters from data.
Eg name
1.2-KPIA1-App-00001 this is a name i require, but i also require the '1.2' and the 'KPIA1' to be displayed in new columns in the results
i.e. 1.2-KPIA1-App-00001 1.2 KPIA1
*I need this in part of a script as there is thousands of rows of data.
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()
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 am working with a large amount of text data in a single column. I am in the process of pumping that column of data into a delimited text file. The data in the column has new line characters that I need to remove. I need to data to be in a single line in the text file.
So far the things that I have tried are not working. What I try for removing the new line characters?
I have a table of data with lines of various lengths. An example is
A Smith - Give #12345# Sydney City
B Jones and S Jones - Give #876543# Washington
I am trying to work out how to create a new column with just the number between the # symbols. The number would be between 5 and 9 numbers.
best possible way to remove all the characters after a 3rd repetition of a character?
For Example:
I want 10.0.1600.22 to be 10.0.1600
Everything after and including the '.' to be removed.
I understand Substring_Index() is not available whats the other options?
I have a table with code and description as below
create table isin_code
(
code varchar(5),
code_desc varchar(255)
)
go
insert into isin_code values ('aaa','aäsas')
go
insert into isin_code values ('aaa','asâ•šas')
go
insert into isin_code values ('aaa','aâsas')
go
insert into isin_code values ('aaa','asas')
go
I want to identify the list of alt codes available in the table.
I am using SQL Server 2008. In one of my table, one column has values like
"MFY RLHH CSQÉ"
"Aamj Gxmolwn Slf Yytrzgan Hiwd Fnlmyw"
So to fetch the data having only special characters in it, I used below query
Select * From Table Where Column Like '%[^0-9a-zA-Z]%' Escape ' '. Its returning both the records. Here I would like to fetch records for those Unicode characters only which are not within 00201 - 0070E [URL].
I want to remove special characters from a string in sql like <?> in a column value in a table.
View 1 Replies View RelatedI want to create a function that searches for allowed characters within a table range (that contains the allowed characters) and replace any characters outside this range with a space.
For example -
'Bill123?', 'Jones12.z-'
'John&12/', 'QWERT123&4'
Wanted results – the single quotes are there to show the space for the replaced characters.
'Bill123 '
'Jones12.z '
'John&12 '
'QWERT123 4'
Example SQL data
CREATE TABLE [Common].[AllowedCharacters] (
[Character] [varchar](1) NOT NULL,
[Replacement] [varchar](10) NULL,
[AlwaysInclude] [bit] NOT NULL)
GO
SET ANSI_PADDING OFF
[code]....
The function will wrap around the column names and I know it can be done without a table validate the characters but it must be done this way.
I am trying to replace all special characters in a column with one special character.
Example:
Table: dbo.Employee
Column: Name
Name
-------
edwardneuman!"<]
mikemoreno)'>$:
JeffJensen"?>"
I am trying to get the namepart to the left of ANY special character. To achieve this, I am thinking of replacing all the special characters with a single special character so that I can find the first occurrence of that special character and grab left of the special character (SUBSTRING/CHARINDEX). This way I don't need to loop through all the special characters.
I am expecting the following results:
Name
-------
edwardneuman<<<<
mikemoreno<<<<<
JeffJensen<<<<
There is a table [Formula_Calc] with formula calculations that need to be replaced with relevant values based on another table [Totals]
[Totals]
RowNo|Total
F1|240
F2|160
F3|180
F11|1000
F12|1500
F13|2000
For example we've got a row from [Formula_Calc] table 'F1+F3' as a string that needs to be transformed as 240+160=400
The below code works for the above example but if I pick 'F11+F3' instead , returns 2561 which comes from 2401+16.
Probably replaces F1 value instead of F11 and adds 1st digit (1) if I got it right ...
DECLARE @formula NVARCHAR(100);
DECLARE @Total NVARCHAR(100);
SET @formula = 'F11+F3';
SELECT @formula = REPLACE(@formula,RowNo,Total)
FROM [Totals]
SET @Total='select '+@formula
EXECUTE sp_executesql @Total;
PRINT @Total;
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?
Thanks
Hi everybody,
I would like to know if there is any property in sql2000 database to separate lowercase characters from uppercase characters. I mean not to take the values €˜child€™ and €˜Child€™ as to be the same. We are transferring our ingres database into sqlserver. In ingres we have these values but we consider them as different values. Can we have it in sqlserver too?
Hellen
Good day experts,
I wonder if i got an answer for this.
How can i iliminate a letters from a set of integers and characters using a SQL Statement
for ex:
ABC9800468F
is that possible?
is there a function that i can use to iliminate them?
I’m getting ASCII characters in one column of my table. So I want to replace same column value in NON ASCII characters.
Note – values in column must be same
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
Arun
Hey you sql programming guru's
I need help seperating a name from first name and last name
The field is like this,
last name, first name
example Doe, John
I need to seperate the last name from the , to the first character
and the last name from the , to the last character.
I think I have to use a substring but not sure how tell it to stop and
start when it gets to the comma.
Can someone please help me.
Thanks,
Dianne
Need to do something like SUBSSTRING in Access, any ideas?
View 1 Replies View RelatedHi
Can anyone show me how to do the following:
I have this snippet of data
ids
-----------------------------------
1582270|1582277
1582270|1582277|1582286
1582270
I want to return the id (the id may not always have the same number of numbers) after the last pipe (|) delimter.
So i want a rs like this :
ids
-----------------------------------
1582277
1582286
1582270
Thanks in advance
Hi,
please help me in developing query to satisfy this...
i have table called test and the table data looks like this.
col1
123.abc
1.ab
12.cba
the query needs to return all rows data after . the result set should return this
abc
ab
cba
appreciate your help..
thanks
sskris
I have a field that contaings 15 characters. I want to just pull the first 6. So anything that matches these 6 will be returned.
View 10 Replies View RelatedWe have entries like below in a table. I need a query by which I can get the CustomerName from the below entries.
ACustomerName
ACustomerNameCredit
ACustomerNameDebit
Thanks!
I an trying gto devide this one field that contains city state and zip into 3 seperate columns. The Column right now looks like this:
Coulumn1
-------------------------------
SOUTH EL MONTE CA91733617
BOSSIER LA71172
GARDENA CA90249107
MILWAUKEE WI53216
PARIS IL61944
DUQUOIN IL62832
REDWOOD FALLS MN56283
AUBURN ME04210
I tryed this:
use cimpro1
select substring(cust_shipto_addr_l3, 1, 19) as 'City',
substring(cust_shipto_addr_l3, 20, 21) as 'State',
substring (cust_shipto_addr_l3, 22, 31) as 'Zip'
from opcshto
For some reason, when I run the query I get this for State:
State
-------------------------
CA91733617
LA71172
CA90249107
WI53216
IL61944
IL62832
MN56283
ME04210
When I use the substring to only pull characters 20 and 21 it pulls everything startign at 20. I just want it to select character position 20 ans 21 for the state. As far as the substring for City and Zip, everything comes out fine. Its just State that I am having trouble with.
Any help is appreciated.
iam trying to write a string function which will give me the id part of a mail id
but iam geting the string along with @ and when iam trying to remove the last char (@) iam getting error
query:
select substring(leadassignedtombemail,1,(CHARINDEX('@', leadassignedtombemail)))
from lead_details -----> Gives me id along with @
select substring(leadassignedtombemail,1,(CHARINDEX('@', leadassignedtombemail) - 1))
from lead_details ------------>gives me error "Invalid length parameter passed to the substring function."
But
select (CHARINDEX('@', leadassignedtombemail) - 1) from lead_details
works and gives me the length of id without counting @
where did i go wrong
i am trying to get the last name of the customer, but my db has the names stored as (first,middle, last) order in a single field. i am using the statment:
,RIGHT(ActCustName,LEN(ActCustName) - CHARINDEX(' ',ActCustName) ) AS LAST
but it only works if the customer does not have a middle name, otherwise it returs the middle+last as the last name. what should i do/ any ideas??? here is my code
select ActPrjMgr
,ActEmpId
,ActEmpName
,ActCustName
,RIGHT(ActCustName,LEN(ActCustName) - CHARINDEX(' ',ActCustName) ) AS LAST
,ActPrjCode
,left(ActPrjType,2) as Status
,ActEmpTaskCode
,left(ActBillingPeriod,11)as ppedate
,left(ActivityDate,11) as actdate
,ActTimevalue
from dbo.ACTIVITIES
where ActBudCat = 'labor'
and ActBillingPeriod = '11/17/2006'
and actprjcode <> ' '
and actprjcode is not null
--and ActBillingPeriod = @StartDate
order by ActPrjMgr
,ActEmpID
,ActEmpTaskCode
,ActivityDate
Hi All
Iam new to sql, Iam using sql 2000 and have a field datatype varchar.
An example of the string: -
CN=Leighton Morgan/OU=WR/O=Extranet
I need to write a query that will allow me to extract the following:-
Name: Leighton Morgan
Company: WR
and lastly the word Extranet
Please can you help
Many Thanks
Hi All
Iam new to sql, Iam using sql 2000 and have a field datatype varchar.
An example of the string: -
CN=Leighton Morgan/OU=WR/O=Extranet
I need to write a query that will allow me to extract the following:-
Name: Leighton Morgan
Company: WR
and lastly the word Extranet
Please can you help
Many Thanks
Hi All
Iam new to sql, Iam using sql 2000 and have a field datatype varchar.
An example of the string: -
CN=Leighton Morgan/OU=WR/O=Extranet
I need to write a query that will allow me to extract the following:-
Name: Leighton Morgan
Company: WR
and lastly the word Extranet
Please can you help
Many Thanks
i have a column that has "Full name" of an individual.
I want to create a new column with just the first name.
How do i populate this column with just the first name...ie it just takes the string until the space from the "full name" column.
can anyone help me with the following report.
Create procedure Rpt_StaffDevelopmentReport
(@cmb1 as varchar(100),
@cmb2 as varchar(100) with encryption as
Begin
--@cmb1 is the site name (s.sitename), @cmb2 is the staffname in the form €˜surname,forename€™
--@cmb2 will need to be split based on the €˜,€™ within the string
--tables needed are staffDevelopment , staffMember and Site
end
Three Tables.
Site
Columns
Siteid, sitename, address1, address2
Staffmember
Staffed, surname, forename,sex, address1, address2, siteid
Staffdevelopment
Staffed, development, personal
Hi
In my status table I m having field like
Actionby (COLUMN NAME)
-------------------
TravelDept
TravelDept
Approver
FinanceDept
TravelDept(xyz@yahoo.com)
I m having mail id along with TravelDept in some rows.
I want to retreive rows containing TravelDept.When i am retreving i wnat to retreive value 'TravelDept' which is having mail id also.
i have written one query,but its retreving only the value which is having mail id.
i want to retreive TravelDept value with mail id and Only TravelDept values also.
my query is:
select replace(SUBSTRING(S.Actionby,CHARINDEX('',S.Actionby,1) , CHARINDEX('(',S.Actionby,1)+0 ),')', '') from status S;
But this query retreiving only 1 row only insteadof 3 rows.
Total 3 rows having TravelDept value.I want to retreive all these 3 rows.
please help me.
Thanks.