Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Shriram_Sridhar
Partner - Contributor III
Partner - Contributor III

Remove number and decimals in a string

Hi All,

I have a requirement where I need to remove numbers and decimals from a string. The string contains a list of software names and their version number.

Eg:

Adobe Acrobat read x64 12.1.3.4
1tools 6.5.11
Windows 7 Sp3 3.1

I know I can use keepchar() function but it removes all numeric values in the above string. My requirement is only to remove the version numbers.
The result should be;

Adobe Acrobat read x64 
1tools 
Windows 7 Sp3 

Thanks in advance!

Labels (3)
3 Replies
MayilVahanan

Hi @Shriram_Sridhar 

Try like below

LOAD *,Left(software, index(software, ' ', SubStringCount(software, ' '))-1) as software1 Inline
[
software
Adobe Acrobat read x64 12.1.3.4
1tools 6.5.11
Windows 7 Sp3 3.1
];

 

output:

mayilvahanan_0-1603779648692.png

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Shriram_Sridhar
Partner - Contributor III
Partner - Contributor III
Author

Hi Mayil Vahanan,

Thanks for the solution. It helped me to exclude the last word in the string. I have another scenario where it does not work.

Eg:
NI Xerces Delay Load 2.7.7 64-bit - here the version number is between the string.
How should I handle both these cases? is there a function that can eliminate a number and decimal format like ##.##.## 

Thanks for your time help ! 🙂 

Kushal_Chawda

@Shriram_Sridhar  Regular expression will be useful here and will be better approach here. You can use regular expression connector which is pat of the web connector package. Look at below

https://help.qlik.com/en-US/connectors/Subsystems/Web_Connectors_help/Content/Connectors_QWC/Data-So...

But you can try below. Assuming '.' is used with version number only and not used in any other text. Otherwise this logic won't work.

 

LOAD *,
     trim(replace(Software,mid(Software,Index(Software,'.')-2,(Index(Software,'.',-1)+2)-(Index(Software,'.')-2)),'')) as Software_new;
load * Inline [
Software
NI Xerces Delay Load 2.7.7 64-bit
Adobe Acrobat read x64 12.1.3.4
1tools 6.5.11
Windows 7 Sp3 3.1
]

 

Screenshot 2020-10-27 112514.png