Following function in not giving me correct output:
update dbo.raw set PhnNumber = RIGHT(calling_pty,10)
What I want is....if in case column "calling_pty" i have values like 911111111111 so it should give me the output in column "PhnNumber" as 1111111111 means only right 10
select hl, substring(hl,1,patindex('%/%',hl)) as test from appointment
returns
hl test A PCM/RODRIGUEZ A PCM/ Y OPTOMETRY/VISUAL FIELD TESTSY OPTOMETRY/ W DENTAL/DUNDON W DENTAL/ Y LAB Y PCM/NEMES F/U Y PCM/ W NUTRITION/FIRM-A (ROOM E116)W NUTRITION/ W FIRM-A/SILVER/ABBOUD IIW FIRM-A/
I want to be able to remove the first 2 digits and the / to just have the clinic only remaining. Note that Y LAB is not listed in the test column..why? Any help is greatly apprecitated. Thank you....
How can I do this in TSQL, there are alot of wild chars but I could not find how to count them, in case below I only want id =1 and 2
this sql is not correct yet...
DECLARE @a VARCHAR(22) = ' alpha1234 qwerew', @b VARCHAR(22) = '3456_Bravo', @c VARCHAR(22) = ' only_three123_Delta' --SELECT 1 id, @a c UNION select 2 id, @b c UNION SELECT 3 id, @c c SELECT * FROM (SELECT 1 id, @a c UNION select 2 id, @b c UNION SELECT 3 id, @c c) b WHERE c LIKE '%[1_3]%'
I am looking for a solution to move all Part Numbers for a specific manufacturer that is 9 digits long to a new column.
Let me explain............
I have a product table that has three columns of product codes associated with that product (Part Number as PN, Series as Series, and Industry Code as ICDE). I now want to create a fourth column (Manufacturer Code as MCDE) as my database has grown for another product specific numerical designator that is specific for one manufacturer.
Currently I have the specific 9 digit codes in the same column as the Part Numbers, though they all have their specific rows (Part Numbers & Manufacturer Codes are not in the same cell).
Now the Part Numbers have various numbers, letters, and special characters, but the specific 9 digit manufacturer codes are pure numbers.
So my question is............
How does one go about moving these specific 9 digit codes to their new column and out of the Part Number column?
They will all be exactly 9 digits, no special characters, no letters, no spaces.
Is there a way to tell MS SQL to just move anything for that specific manufacturer that has the 9 digit manufacturer numbers to the new column, bypassing anything with letters, special characters or any part number that is not a pure 9 digit number?
Hi,I recently had to add a new column to a table that resides off-site,in a customer's environment. Previously, the table had around 300columns, all of which were in alphabetical order.I found that the ALTER TABLE command appeared to be only capable ofappending a new column, but wasn't capable of inserting one into themiddle of the table.Since alphabetical order wasn't a requirement, but just a "nice tolook at" feature, I appended it anyway, and forgot about it.Anyone know of a clever way to do this though?Curiously,Warren WrightScorex Development Team
I created a ssis package which exports the data from oledb source to flat file (csv format). For this i have OLEDB source and Flat File as destination. I generate the file and filename dynamically with the column names in the first row. So if the dynamically generated file name already exists , then i want to append the data in the same existing file. But I dont want to append the column names again. I just want to append the rows to the existing rows.
so lets say first time i generate a file called File1_3132008.csv.
Col1, Col2 1,2 3,4
After some days if my ssis package generates the same file name i.e. File1_3132008.csv, this time i just want to append the rows to the existing file. So the file should look like this- Col1, Col21,23,45,67,8
But instead my file looks like this if i set Overwrite propery to false
Col1,Col2 1,2 3,4 Col1,Col2 5,6 7,8
Can anyone help me to get the file as shown in the highlighed
Hi,This is not SQL Server database problem, however, net search has failedto generate a solution to the problem, I'm trying my luck at this NGnow.Problem, remove the special character, [ from text.e.g. text ="this is [1] and [stuff] and some [2] and hat [3] and dog"Desired result ="this is 1 and [stuff] and some 2 and hat 3 and dog"I know [[:digit:]] would find all the instances of digits insidebrackets such as [1], [2], [3]. However, I don't know how to get ridof the special characters of [ and ].Any regular expression expert out there?Thanks.Don
I have a table for products and the price field is smallmoney data type. When I view the data stored it shows it with millionths. How do I get it to store only hundredths?
Hi all,I have a number like so 778625 and want to make this a more meaningfuldate (UK) so how difficult is it to add two digits in this case 19 tothe front of this number?Many thanksSam*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
is there any builtin functions or any other ways to split digit and numbers in sql server2005. for example in Patel1234 , i am want to split patel and 1234 seperately. thanks,
I would like to find all the records that contain only digits. So far, I have this:
SELECT * FROM tmp1 WHERE (word LIKE N'[0-9]')
It returns only ten results, each containing a single digit. What I need is to find all the records of any length containing only digits, like '378', '2005', etc. but not records containing both digits and other stuff (e.g. letters) like 'I95' or 'P2P'.
I need to take a numeric value and add together the cubes of each single digit. The string length may vary from row to row.
As as example the number 1234 has individual cube values of 1 (1 cubed), 8 (2 cubed), 27 (3 cubed) and 64 (4 cubed). The resulting sum of the cubes is then 1+8+27+64 = 100. The same result would occur for 1243.
The purpose is to look for numeric values in a table where the same digits occur in two different rows but in a different order.
How can I create a function to apply to a varchar column to produce my integer result ?
I am creating a view to pull data for a UPS integration I am doing. I currently have this view where I pull my data from. All is well accept for my PATINDEX,
Currently I have this PATINDEX So when a user puts this into delivery instructions "#999999" UPS_FINAL returns 999999 which is good for me to use. But The PATINDEX will also grab all kinds of neat stuff out of the delivery instructions field when I really only want whatever is after the # sign in the field.
I am new to sql and dont quite understand how this search is working but I think I am in need of a better way to search the field
Code:
WHEN SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) IS NULL
THEN dbo.Address_Table.ups_code
ELSE SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) END AS UPS_FINAL
------------------------------------------------------------------------------------- What I am looking to accomplish is a SUBSTRING that will search delivery_instructions
Code:
SELECT
TOP (100) PERCENT P21PLAY.dbo.p21_view_oe_pick_ticket.pick_ticket_no, P21PLAY.dbo.p21_view_oe_hdr.order_no, P21PLAY.dbo.p21_view_oe_hdr.customer_id, P21PLAY.dbo.p21_view_oe_hdr.ship2_name, P21PLAY.dbo.p21_view_oe_hdr.ship2_add1, P21PLAY.dbo.p21_view_oe_hdr.ship2_add2, P21PLAY.dbo.p21_view_oe_hdr.ship2_city, P21PLAY.dbo.p21_view_oe_hdr.ship2_state, P21PLAY.dbo.p21_view_oe_hdr.ship2_zip, P21PLAY.dbo.p21_view_oe_hdr.po_no, P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id AS Carrier, P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id AS Supplier,
P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) AS UPS_Shipper,
------------------Checks view for email address or assigns alternate------------------ (CASE WHEN charindex('@', p21_view_contacts.email_address) > 0 THEN p21_view_contacts.email_address ELSE
'email@domain.com' END) AS alternate_address,
'Y' AS QVN, 'email@domain.com' AS failureaddress,
P21PLAY.dbo.p21_view_contacts.email_address,
------------When carrier_id is not one of these # then Null; else ------------------------------
CASE WHEN P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id NOT IN (105188, 105191, 105194, 105197, 105200,
105203, 105206, 105209, 105212) THEN NULL
----------------- Looks for special Character in delivery_instructions; if NULL then ups_code; ELSE return value from delivery_instructions as UPS_Final--------------------
WHEN SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) IS NULL THEN dbo.Address_Table.ups_code ELSE SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) END AS UPS_FINAL
FROM dbo.Address_Table INNER JOIN P21PLAY.dbo.p21_view_oe_pick_ticket INNER JOIN P21PLAY.dbo.p21_view_oe_hdr ON P21PLAY.dbo.p21_view_oe_pick_ticket.order_no =
P21PLAY.dbo.p21_view_oe_hdr.order_no ON dbo.Address_Table.id = P21PLAY.dbo.p21_view_oe_hdr.customer_id LEFT OUTER JOIN P21PLAY.dbo.p21_view_contacts ON P21PLAY.dbo.p21_view_oe_hdr.contact_id = P21PLAY.dbo.p21_view_contacts.id
WHERE (P21PLAY.dbo.p21_view_oe_hdr.completed <> 'Y') AND (P21PLAY.dbo.p21_view_oe_hdr.delete_flag <> 'Y') AND (P21PLAY.dbo.p21_view_oe_hdr.will_call <> 'Y') ORDER BY P21PLAY.dbo.p21_view_oe_pick_ticket.pick_ticket_no
I have a column which needs to be 9 digits long. The first four digits are independent from the other digits. The following digits need to be 5 and I have to add leading zeros if they're less than 5. This is an awful design and I'd love to change it but I can't.
Hi! Every one, I need to increase my numeric digit in a table.previous column name was "Amount",Precision=18 and scale=6, when I increase Precision=28 it show "Airthmatec Error", even when increase precision in a new table it work properly.
Is the merge method, what will work in this case ? I have two datatables with the exact same structure. How can I append the rows from table 2 onto the bottom of table 1 ? Is looping through the rows collection the only way ?
I am trying to append the current row ID to a string I am trying to insert via a sproc. I have retrieved the @@Identity and I am passing it into a class with a parameter and calling it using:
Listings.UpdateDB AddNewListing = new Listings.UpdateDB();
I know this must be simple, but I am stumpped, please help!
I am writing a stored procedure in SQL 2000 where an incomming variable is a string of characters (a couple of sentences) and I want to add that to the existing string of characters in a table field called "Comments".
I do not know how to append the text in a field. How is that best done?
The basic function of the procedure is to take whatever string is passed to it and append it to the current contents of the field "Comments". As the procedure is ran over and over again, the field is constantly appended with the incomming text.
What is the best way to do this? Can anyone give me an example?
I know this must be simple, but I am stumpted, please help!
I am writing a stored procedure in SQL 2000 where an incomming variable is a string of characters (a couple of sentences) and I want to add that to the existing string of characters in a table field called "Comments".
I do not know how to append the text in a field. How is that best done?
The basic function of the procedure is to take whatever string is passed to it and append it to the current contents of the field "Comments". As the procedure is ran over and over again, the field is constantly appended with the incomming text.
What is the best way to do this? Can anyone give me an example?
