SQL 2012 :: Picking Number String Out Of Text String
Jul 14, 2015
I have a text field which has entries of variable length of the form:
"house:app.apx&resultid=1234,clientip"
or
"tost:app.apx&resultid=123,clientip"
or
"airplane:app.apx&resultid=123489,clientip"
I'm trying to pick out the numbers between resultid='...',clientip no matter what the rest of the string looks like. So in this example it would be the numbers:
1234
123
12389
the part of the string of the form resultid='...',clientip always stays the same except the length of the number can vary.
How do I display numeric data as text string?? I need the report to spell out the number. 1 would read as One, 2 as Two ect. For example writing the amount on a check. Need to do in SSRS.
add a number to the end of an ID to create a series.For example, I have an EventID that may have many sub events. If the EventID is 31206, and I want to have subEvents, I would like have the following sequence. In this case, lets say I have 4 sub Events so I want to check the EventID and then produce:
312061 312062 312063 312064
How can I check what the EventID is, then concatenate a sequence number by the EventID?
I'm trying to parse out a line of data that is separated by the text "atc1.", "atc2." etc.
For example,
[atc1.123/atc2.456/atc3.789/atc4.xyz/]
If I only want the data after atc2., then I could search the string for "atc2." and collect all the characters afterwards. But how can I make sure to trim off all the data after "atc3." to make sure I'm only collecting "456" from the example above?
We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.
My requirement is that if the string in the column has any of the characters from 'ACDIPFJZ' , those characters have to be retained and the rest of the characters have to be removed.
I have a parameter value as shown below and this is dynamic and can grow
Example : 101-NY, 102-CA, 165-GA 116-NY, 258-NJ, 254-PA, 245-DC, 298-AL How do I get the values in the below format NY,CA,GA --- each state to be followed with comma and the next state NY,NJ,PA,DC,AL --- each state to be followed with comma and the next state
correct query that will fetch only state names and not the numbers.
I have a string in form "abcdefg 12355 ijklmn"Now I want get only the number 12355 within the string !!Is there any function available in T-SQL of Sql server 2K??Thanksfor any help
I've just upgraded from Access to SQL 2000 and am having problems with a line of SQL code.
In Access I sorted on a text column of part numbers where a number within the text was the important sort value.
Text was typically "AB 1234 A" "AB 34 B" "AB 73000" The important sort has to be on the numeric value of the part number.
Access Code that worked was: ORDER BY Val(MID(Parts.PartNo,4,6))
Seeing both Val() and MID() are not acceptable in SQL 2000 I ended up using Substr(Parts.PartNo,4,6) but this returns a string sort where 100 appears before 25 etc.
Can anyone help point me in the right direction? Many thanks Peter
Each unit NAME and part NAME is unique. So what kind of performance would I be losing if I were to do something like:
PROCEDURE addPartToUnit( VARCHAR(20) sPartName, VARCHAR(20) sUnitName ) -- Now in this procedure i'll just get the id's via the names from a select statement
Versus
PROCEDURE addPartToUnit( nPartId INTEGER, nUnitId INTEGER ) -- In this proc, i directly input the primary keys of the table so no lookup is necessary
The reason is that I would like for developers to be able to write code without having to know the id's.
How do I read only the number starts with ID and pid from the above string and insert into column 'ProID' of the same row? It is log table tracking all the visiters to the web site. The number next to IDs and pids are productIDs. So if I manage to get product ID to the column ProId then I can hit against other table to find out which product the customer clicked.
I planned to do the above said by trigger. Please help
Hi,I want to get the string representation of a hex number from avarBinary column of a table.For example I want to get the output : 'The Hex value is 0xFF'butselect 'The Hex value is ' + convert(varchar(10), 0xFF)retruns the ascii charecter for 0xFFAny idea how do I get the hex form as it is?thanks.Supratim
Hi, ORDER BY will sort string (varchar) field which "11" < "2", is there any way I can sort them as number so "11" > "2"? what if there is character inside, like "11a", "2b", in this case, it should sort like, "1" "2" "11" "1a" "2a" "11a" "1b" .... Is this possible? also, I tried to google this but not find anything help. what's best keyword to use?
I am trying to convert some strings to number datatype. Some numbers are like this. 1,45.45- 21.21- 4,611.20- Is it possible to convert these type of strings to number datatype. Thank you.
Lets say I have a table named Projects with the data below.Y07001Y07002Y07003Y07010Y07011I want to pick up the last 3 numbers and add a one to it so. Declare @Count as varchar(3) SELECT Top 1 @Count = Right(Project, 3)+1 FROM Projects Order by ID desc SELECT @Count This is what I am using. It picks up the last entry, add a one to it and gives me 12 in return. What i want is 012, not 12
In short: I want my following problem to work with a LIKE instead of exact match and if possible be faster. (currently 4s)
Problem: I got a set of rows with varchar(50), spread out over multiple tables. All those tables relate to a central Colour table. For each of the columns, I want to match the values with a set of strings I insert and then return a set of Colour.Id
E.g: input: 'BLACK', 'MERCEDES', '1984' Would return colour ids "025864", 45987632", "65489" and "63249" Because they have a colour name containing 'BLACK' or are on a car from 'MERCEDES' or are used in '1984'.
Current Situation: I) Create a table containing all possible values CREATE TABLE dbo.CommonSearch( id int IDENTITY (1, 1) NOT NULL, clr_id int, keyWord varchar(40), fieldType varchar(25) And fill it with all the values (671694 rows) ) II) Stored Procedure to cut a string up into a table: CREATE FUNCTION dbo.SplitString ( @param varchar(50), @splitChar char = '' ) RETURNS @T TABLE (keyWord varchar(50)) AS BEGIN WHILE LEN( @param ) > 0 BEGIN declare @val varchar(50) IF CHARINDEX( @splitChar, @param ) > 0 SELECT @val = LEFT( @param, CHARINDEX( @splitChar, @param ) - 1 ) , @param = RIGHT( @param, LEN( @param ) - CHARINDEX( @splitChar, @param ) ) ELSE SELECT @val = @param, @param = SPACE(0) INSERT INTO @T values (@val) END RETURN END III)Stored Procedure to query the first table with the second one CREATE PROCEDURE [dbo].[GetCommonSearchResultForTabDelimitedStrings] @keyWords varchar(255) = '' AS BEGIN SET NOCOUNT ON; select clr_id, keyWord, fieldType from dbo.commonSearch where keyWord in (select * from splitString(@keyWords, '')) END
So, how can I use a LIKE statement in the IN statement of the last query. Furthermore, I was wondering if this is the best sollution to go for. Are there any better methods? Got any tuning tips to squeeze out an extra second?
I've got a string of numbers, say 123456 (the actually number is 12 digitslong). I need to calculate the sum of each individual number in the stringof numbers, so in the example of 123456 the sum would be 21 (1+2+3+4+5+6).Thanks
I am importing from a comma delimited text file. A certain string column has values that represent numbers, such as "US$ 20", "30", "40 - 50". I would like to import the number, and if the cell does not contain a string representing a number (example: "I don't know"), a NULL value should be imported instead for that cell.
Hello experts, Suddenly I find myself in need of a SQL search that ONLY starts at a record. I need to page through the existing records, but start at the users choice.
I was wondering what would be the best way to remove special characters like, '-', '&' '(',')','#','*', etc... from a number string. To be specific a phone Number string where the string is >= 10.
For the above string, which string function can be used to find the number of occurences of a particular character, for example, ','? For this example, the answer should be 4. Is there any built-in function in SQL to do this?
In t-sql 2008 r2, I would like to know how to select a specific string in a varchar(50) field. The field in question is called 'CalendarId'.
This field can contain values like:
xxIN187 13-14 W Elem HS321 13-14 D Elem IN636 13-14 C Elem 030 13-14 clark middle.
What I am looking for is the first position that contains a number value for the length of 3. Thus what I want are values that look like the following: 030, 636, 187.What I know that I want is substring(CalendarId,?,3).The question mark is where I want the starting location of a number value (0 to 9) of the value in CalendarId . I tried pathindex but my syntax did not work.
I have a query which runs exactly as I want it to. Let's say it returns this in a table:
IDNumber 123 212 312
I want to change (find and replace?) all the number 12s in the 'Number' column for a string. I want to change the 12s to the word: HelloWorld. I don't want to update the table, merely the table that is being returned in the query.
I'm new to SQL and I've been trying this for a while now.
I have let's say a loop of numbers from one to ten. It appears like this:
1 2 3 4 5
What I want to do is to have it appear on one column like this: 12345, the problem is I can't seem to figure out if I need to only have one variable or I'm missing something else, I figured you need to convert them into a string character but I'm still unable to do it.
