Substring Questioin
Aug 15, 2006
declare @bid table(
streetNumber varchar(10),
streetName varchar(100),
streetNameSuffix varchar(100)
)
insert into @bid (streetName)
select '12 union street' union all
select '1A apple avenue' union all
select '11A apple st avenue road' union all
select 'POB 23'
select * from @bid
update @bid
set streetNumber = case
when substring(streetName,1,charindex(' ',streetName)) like '[1-9]' OR
substring(streetName,1,charindex(' ',streetName)) like '[1-9][1-9]' OR
substring(streetName,1,charindex(' ',streetName)) like '[1-9][1-9][1-9]'
then substring(streetName,1,charindex(' ',streetName))
else null
end,
streetNameSuffix = case
when substring(streetName,1,charindex(' ',streetName)) like '[1-9][a-z]' OR
substring(streetName,1,charindex(' ',streetName)) like '[1-9][1-9][a-z]' OR
substring(streetName,1,charindex(' ',streetName)) like '[1-9][1-9][1-9][a-z]'
then substring(streetName, 1, charindex(' ', streetName))
else null
end,
--streetName = substring(streetName, charindex(' ',streetName) + 1, len(streetName)-charindex(' ',reverse(streetName))-charindex(' ', streetName)) + ' ' +
--substring(streetName,len(streetName)-charindex(' ',reverse(streetName))+2,len(streetName))
streetName = substring(streetName, charindex(' ', streetName)+1, len(streetName)-len(charindex(' ', streetName)))
select * from @bid
how can i write my code in order for the "POB 23" to stay the in streetName field?
View 12 Replies
ADVERTISEMENT
Aug 10, 2007
Hello All,
I have a question that I hope that you all can help me answer. I have a customer that is using Cisco Call manager to import Client Matter Codes into his call manager. To import the CMCs, he uses the Bulk Administration tool or BAT to import them via a .csv file. What he wants to do is automate the import. Well, Cisco doesn't currently have a way to automate this import so I had the thought that maybe we could import the text directly into the CMCs table in the SQL database. I did a little research and found out that SQL has an import/export tool call bcp. I used bcp to export the table and here's an example of one line in the table:
1111111111111-1111111111-1111111111111 123456 Plaintiff vs. Defendant 111111111-1111-1111-11111-1111111111111
And here is a sample of the file that we are import using the BAT:
123456,"Plaintiff v. Defendant"
I created this format file:
8.0
4
1 SQLUNIQUEID 1 16 "" 1 pkid ""
2 SQLCHAR 0 16 "" 2 Code SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 50 "" 3 Description SQL_Latin1_General_CP1_CI_AS
4 SQLUNIQUEID 1 16 "" 4 msrepl_tran_version ""
I did some reading and if I understood correctly I need to do the import using the format file above, but I need to map the import so it only imports into the two columns I need, so I edited the file:
8.0
4
1 SQLCHAR 0 16 "" 2 Code SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "" 3 Description SQL_Latin1_General_CP1_CI_AS
I'm a sql newbie so I just want to know if this will work and if the import does work will sql automatically generate the first two columns (SQLUNIQUEID) during the import. Also after the import do I need to do anything like restart the SQL service. Any help you guys can give me will be appreciated.
View 3 Replies
View Related
Jul 5, 2001
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
View 2 Replies
View Related
Nov 5, 2001
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
View 1 Replies
View Related
Aug 8, 2001
Need to do something like SUBSSTRING in Access, any ideas?
View 1 Replies
View Related
Oct 4, 2004
Hi
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
View 9 Replies
View Related
Feb 4, 2005
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
View 1 Replies
View Related
Feb 3, 2004
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 Related
Apr 8, 2004
We 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!
View 14 Replies
View Related
Jun 2, 2004
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.
View 7 Replies
View Related
Sep 13, 2006
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
View 2 Replies
View Related
Dec 1, 2006
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
View 7 Replies
View Related
Mar 19, 2007
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
View 9 Replies
View Related
Mar 19, 2007
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
View 1 Replies
View Related
Mar 19, 2007
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
View 1 Replies
View Related
Jan 25, 2006
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.
View 5 Replies
View Related
Oct 22, 2007
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
View 7 Replies
View Related
Feb 1, 2007
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.
View 2 Replies
View Related
Aug 31, 2007
I have a SQL column that contains something like this:
ORLANDO ,FL. 32803
COCOA , FL. 32922-8617
SATELLITE BCH,FL 32937-3523
TAMPA , FL. 33609-3105
EAU GALLIE ,FL. 32935
I need only the name of the city, dont need FL or the zip code, how can I do a substring statement on sql to get only that?
Thanks,
Erick
View 8 Replies
View Related
Oct 26, 2007
I'm using the GridView to display some accounting infromation. I have a project where I have a 14 character control number. I would like to have a dropdown list to select the account classification of records to be displayed. The accounting classification is the first two characters of the control number. So the dropdown list needs to show unique first two characters and the GridView will be filtered on these two characters. I have been trying to use "substring" in the ASP.NET code; not even sure you can.
Any suggestions on how to accomplish this would be greatly appreciated. See code below:
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2"
DataTextField="control_num" DataValueField="control_num">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:GPCRReportsConnectionString %>"
SelectCommand="SELECT DISTINCT [substring(control_num,1,2)] FROM [Request]"></asp:SqlDataSource>
ERROR: Invalid column name 'substring(control_num,1,2)'.
View 6 Replies
View Related
Dec 11, 2007
I tried to mimic some code I found but on RequestDateTimeSUBSTRING(RequestDateTime,(charindex(':',RequestDateTime)+1),len(RequestDateTime)) AS 'Date Downloaded'I can only use SUBSTRING on char type strings.How would I get maybe only up to the first : character in the RequestDateTime?
View 2 Replies
View Related
May 6, 2008
hi frenz:
Can any one send me the code for
substring multiple cell values of same column and put it into one cell of that column.
View 3 Replies
View Related
May 29, 2008
Hi I am trying to update a part of a date string using the following sql statement. Alas, I'm having no joy.cmd.CommandText = "UPDATE tb_bookings SET SUBSTRING(startDate,12,5) = @newStartTime WHERE requestPackID = @reqPackID"; The date has been saved in the db in the following format:Fri May 23 17:30:00 GMT+0900 2008 Any idea why this isn't working. Any help much appreciated.Barry
View 4 Replies
View Related
Jan 28, 2006
Hi,
I'm trying to do write a query in SQL that returns the last two characters in a field.
If the actual fields are sheep, dog, cat, parrot
I want the query to return ep, og, at, ot
After rooting around I've discovered the SQL 'substring' function.
The problem I have is that the field has a variable length, so I need a way of passing the strign length to substring.
Its probably simple to solve, but not for a rookie like me without an example and I am (as always) grateful for any ideas.
View 3 Replies
View Related
Apr 11, 2002
Hi Carmi & Jun,
Thanks for responding. The problem is still persistent. What I want to do is to display two names in a combobox and the box should only be as wide as 2 adjacent names for their actual length. Whether I use char or varchar the combobox is always twice the width defined for the fields. So we will try to find a solution: an elegant one rather than a workaround.
Any new ideas are still welcome.
Thanks again.
Dirk
View 2 Replies
View Related
Feb 18, 2000
I am looking for a way to return only the numbers from the following example:
Column1 varchar(255)
Column1
---------------------
10 rows copied.
55004 rows copied.
1000004 rows copied.
Once I get the numbers I will then convert (Cast) them as Int.
Thank you in advance,
tw
View 1 Replies
View Related
May 26, 2005
Hi All,
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
Can you help? Thanks.
View 5 Replies
View Related
Jun 20, 2008
it's early in the morning (well it is here anyway) and i need a shot of logic...
in the data base i'm currently working with i have a varchar field that holds either a number or a collection of numbers in square brackets. I need to extract the number or the first number in square brackets if it's a list. i know it's gonna be a simple one but my head just won't do it?? i'm trying with substring and charindex to determine the position of the '[' but just not getting it this morning
quick sample of what the data in this column may look like...
declare @t table (col varchar(30))
insert into @t
select '2' union all
select '[5] [4]' union all
select ' [12] [1]'
so i need to get...
col
-------
2
5
12
EDIT-
OK, so i get this to work but only if there is actually square brackets
declare @t table (col varchar(30))
insert into @t
--select '2'union all
select '[5] [4]'union all
select ' [12] [1]'
select col
,substring(col, charindex('[',col)+1, charindex(']',col)-charindex('[',col)-1)
from @t
Em
View 20 Replies
View Related
Sep 24, 2013
We capture the firstname of a person along with their middle intial in our contacts table . How do i use the substring function to get just the firstname and omit the middle initial
Sample Data
Jennifer K
Jason R
Samuel V
I want just the firstname without the space and middle initial
Required Format
Jennifer
Jason
Samuel
View 4 Replies
View Related
Mar 5, 2008
name
--------------------------------------
copd_update_sept2007 9/25/2007 3:24:50 PM
copd_update_sept2007 9/26/2007 1:18:07 PM
feb2008_v2_n2 2/25/2008 1:56:41 PM
feb2008_v2_n2 2/26/2008 2:28:05 PM
i want string like till '' blank spaces..
how can i get that
select substring(name,1,15) from mymaxiddate
View 10 Replies
View Related
Mar 20, 2006
I need help capturing information from a free text field.The 10 examples below contain examples of the information I am tryingto capture.In each cell I am trying to capture the number between 'TranID=' andthe next '&'.So in the fisrt cell I would like to capture 14078800.Things you will need to keep in mind are;The number is not of fixed length.'TranID' will always precede the numberThe number will always be followed by an '&'The '&' sign can occur multiple times in the text.Code=web.co.uk%product_250p&TranID=14078800&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=delivCode=web.co.uk%product_free&TranID=14077576&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=delivCode=web.co.uk%product_250p&TranID=14077583&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=delivCode=web.co.uk%product_250p&TranID=14077584&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=delivCode=web.co.uk%product_150p&TranID=14077579&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=delivCode=web.co.uk%product_250p&TranID=14077603&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=delivCode=web.co.uk%product_250p&TranID=14077741&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=delivCode=web.co.uk%product_250p&TranID=14077757&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=delivCode=web.co.uk%2Fpush_wallpaper_250p&TranID=14077770&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=delivCode=web.co.uk%product_250p&TranID=14077604&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=delivRegards,Ciarán
View 8 Replies
View Related
Jul 19, 2007
hi allI want to write a sotred procedure which has a parameter,called@name.The SQL statement must return all rows where this parameter is asubatring of a special column called FirstName.This procdeure do thesame ,but return all rows where @name is the exact matching.whatshould I do?/*This procedure return the exact matching*/CREATE PROCEDURE substring@name varchar(50)ASSELECT *FROM table1WHERE (table1.firstName LIKE @name);
View 2 Replies
View Related
Nov 13, 2007
Hello,
Does anyone have some code that can do the following:
Postcode N12 5FF
Postcode NG4 5FF
I need to get the postcode N12 5FF based on the 'N' bt leave the NG4 5FF postcode behind. So obviously using LEFT will not work, does any have any code that will strip of the first alpha characters?
Thank you.
View 7 Replies
View Related